Operators in SQL – Alias, IN and Between

Keeping you updated with latest technology trends, Join DataFlair on Telegram

1. Operators in SQL

In this SQL tutorial, we are going to see Operators in SQL. Moreover, we will study the IN, Between and Aliases operators in SQL. Also, we will discuss these SQL Operators with syntax and example

So, let us start exploring Operators in SQL.

Operators in SQL - Alias, IN and Between

Operators in SQL – Alias, IN and Between

2. Introduction to SQL Operators

The IN SQL Operator is used as a shorthand for multiple OR conditions and also helps to specify multiple values in a WHERE clause.
An Alias SQL Operator is when you need to give a temporary name to a table, or a column. It further makes the columns more readable. One more important point is that it only exists during the time period of a query.
You must read about SQL Clause
The BETWEEN SQL Operator selects values in a given range it is inclusive of the end and begins values, which can be numbers, text, or dates.

3. IN Operator in SQL

The syntax for IN Operators in SQL.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Or

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

Do you know about SQL Injection

a. Examples of IN Operator in SQL

The following SQL statement selects all customers that are located in “Germany”, “France” and “UK”:
SQL IN Operators Example – 

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK
23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
40 La corne d’abondance Daniel Tonini 67, avenue de l’Europe Versailles 78000 France
41 La maison d’Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France
44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany
52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany
57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France
63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France
79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany
84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France
85 Vins et alcools Chevalier Paul Henriot 59 rue de l’Abbaye Reims 51100 France
86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany

Have a look at SQL Index

4. Alias Operator in SQL

The syntax for SQL Alias Operator –

SELECT column_name AS alias_name
FROM table_name;

SQL Table Alias syntax –

SELECT column_name(s)
FROM table_name AS alias_name;

a. Example of Alias Operator in SQL

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

Do you know about SQL Auto Increment
And a selection from the “Orders” table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10354 58 8 1996-11-14 3
10355 4 6 1996-11-15 1
10356 86 6 1996-11-18 2
SELECT CustomerID as ID, CustomerName AS Customer
FROM Customers;

Result –

ID Customer
1 Alfreds Futterkiste
2 Ana Trujillo Emparedados y helados
3 Antonio Moreno Taquería
4 Around the Horn
5 Berglunds snabbköp
6 Blauer See Delikatessen
7 Blondel père et fils
8 Bólido Comidas preparadas
9 Bon app’
10 Bottom-Dollar Marketse
11 B’s Beverages
12 Cactus Comidas para llevar
13 Centro comercial Moctezuma
14 Chop-suey Chinese
15 Comércio Mineiro
16 Consolidated Holdings
17 Drachenblut Delikatessend
18 Du monde entier
19 Eastern Connection
20 Ernst Handel
21 Familia Arquibaldo
22 FISSA Fabrica Inter. Salchichas S.A.
23 Folies gourmandes
24 Folk och fä HB
25 Frankenversand
26 France restauration
27 Franchi S.p.A.
28 Furia Bacalhau e Frutos do Mar
29 Galería del gastrónomo
30 Godos Cocina Típica
31 Gourmet Lanchonetes
32 Great Lakes Food Market
33 GROSELLA-Restaurante
34 Hanari Carnes
35 HILARIÓN-Abastos
36 Hungry Coyote Import Store
37 Hungry Owl All-Night Grocers
38 Island Trading
39 Königlich Essen
40 La corne d’abondance
41 La maison d’Asie
42 Laughing Bacchus Wine Cellars
43 Lazy K Kountry Store
44 Lehmanns Marktstand
45 Let’s Stop N Shop
46 LILA-Supermercado
47 LINO-Delicateses
48 Lonesome Pine Restaurant
49 Magazzini Alimentari Riuniti
50 Maison Dewey
51 Mère Paillarde
52 Morgenstern Gesundkost
53 North/South
54 Océano Atlántico Ltda.
55 Old World Delicatessen
56 Ottilies Käseladen
57 Paris spécialités
58 Pericles Comidas clásicas
59 Piccolo und mehr
60 Princesa Isabel Vinhoss
61 Que Delícia
62 Queen Cozinha
63 QUICK-Stop
64 Rancho grande
65 Rattlesnake Canyon Grocery
66 Reggiani Caseifici
67 Ricardo Adocicados
68 Richter Supermarkt
69 Romero y tomillo
70 Santé Gourmet
71 Save-a-lot Markets
72 Seven Seas Imports
73 Simons bistro
74 Spécialités du monde
75 Split Rail Beer & Ale
76 Suprêmes délices
77 The Big Cheese
78 The Cracker Box
79 Toms Spezialitäten
80 Tortuga Restaurante
81 Tradição Hipermercados
82 Trail’s Head Gourmet Provisioners
83 Vaffeljernet
84 Victuailles en stock
85 Vins et alcools Chevalier
86 Die Wandernde Kuh
87 Wartian Herkku
88 Wellington Importadora
89 White Clover Markets
90 Wilman Kala
91 Wolski

Let’s check SQL Constraints

5. BETWEEN Operator in SQL

The syntax of SQL Between Operator–

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

a. Example of Between Operator in SQL

Below is a selection from the “Products” table in the Northwind sample database:

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 1 2 48 – 6 oz jars 22
5 Chef Anton’s Gumbo Mix 1 2 36 boxes 21.35

The following SQL statement selects all products with a price BETWEEN 10 and 20:

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

Have a look at Primary and Foreign Key in SQL
Result –

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 – 12 oz bottles 19
3 Aniseed Syrup 1 2 12 – 550 ml bottles 10
15 Genen Shouyu 6 2 24 – 250 ml bottles 15.5
16 Pavlova 7 3 32 – 500 g boxes 17.45
21 Sir Rodney’s Scones 8 3 24 pkgs. x 4 pieces 10
25 NuNuCa Nuß-Nougat-Creme 11 3 20 – 450 g glasses 14
31 Gorgonzola Telino 14 4 12 – 100 g pkgs 12.5
34 Sasquatch Ale 16 1 24 – 12 oz bottles 14
35 Steeleye Stout 16 1 24 – 12 oz bottles 18
36 Inlagd Sill 17 8 24 – 250 g jars 19
39 Chartreuse verte 18 1 750 cc per bottle 18
40 Boston Crab Meat 19 8 24 – 4 oz tins 18.4
42 Singaporean Hokkien Fried Mee 20 5 32 – 1 kg pkgs. 14
44 Gula Malacca 20 2 20 – 2 kg bags 19.45
46 Spegesild 21 8 4 – 450 g glasses 12
48 Chocolade 22 3 10 pkgs. 12.75
49 Maxilaku 23 3 24 – 50 g pkgs. 20
50 Valkoinen suklaa 23 3 12 – 100 g bars 16.25
57 Ravioli Angelo 26 5 24 – 250 g pkgs. 19.5
58 Escargots de Bourgogne 27 8 24 pieces 13.25
66 Louisiana Hot Spiced Okra 2 2 24 – 8 oz jars 17
67 Laughing Lumberjack Lager 16 1 24 – 12 oz bottles 14
68 Scottish Longbreads 8 3 10 boxes x 8 pieces 12.5
70 Outback Lager 7 1 24 – 355 ml bottles 15
73 Röd Kaviar 17 8 24 – 150 g jars 15
74 Longlife Tofu 4 7 5 kg pkg. 10
76 Lakkalikööri 23 1 500 ml 18
77 Original Frankfurter grüne Soße 12 2 12 boxes 13

So, this was all in Operators in SQL. Hope you like our explanation.

6. Conclusion – Operators in SQL

Hence, in this tutorial of Operators in SQL, we learned about the three very important SQL Operators. These Operators in SQL are Alias, IN, and Between. Moreover, we discussed syntax and example of Operators in SQL. Still, if you have any confusion, ask freely in comment tab.
See also –
SQL Operators Part 1
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.