Operators in SQL – Alias, IN and Between

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);
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

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');
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
6Blauer See DelikatessenHanna MoosForsterstr. 57Mannheim68306Germany
7Blondel père et filsFrédérique Citeaux24, place KléberStrasbourg67000France
9Bon app’Laurence Lebihans12, rue des BouchersMarseille13008France
11B’s BeveragesVictoria AshworthFauntleroy CircusLondonEC2 5NTUK
16Consolidated HoldingsElizabeth BrownBerkeley Gardens 12 BreweryLondonWX1 6LTUK
17Drachenblut DelikatessendSven OttliebWalserweg 21Aachen52066Germany
18Du monde entierJanine Labrune67, rue des Cinquante OtagesNantes44000France
19Eastern ConnectionAnn Devon35 King GeorgeLondonWX3 6FWUK
23Folies gourmandesMartine Rancé184, chaussée de TournaiLille59000France
25FrankenversandPeter FrankenBerliner Platz 43München80805Germany
26France restaurationCarine Schmitt54, rue RoyaleNantes44000France
38Island TradingHelen BennettGarden House Crowther WayCowesPO31 7PJUK
39Königlich EssenPhilip CramerMaubelstr. 90Brandenburg14776Germany
40La corne d’abondanceDaniel Tonini67, avenue de l’EuropeVersailles78000France
41La maison d’AsieAnnette Roulet1 rue Alsace-LorraineToulouse31000France
44Lehmanns MarktstandRenate MessnerMagazinweg 7Frankfurt a.M.60528Germany
52Morgenstern GesundkostAlexander FeuerHeerstr. 22Leipzig04179Germany
53North/SouthSimon CrowtherSouth House 300 QueensbridgeLondonSW7 1RZUK
56Ottilies KäseladenHenriette PfalzheimMehrheimerstr. 369Köln50739Germany
57Paris spécialitésMarie Bertrand265, boulevard CharonneParis75012France
63QUICK-StopHorst KlossTaucherstraße 10Cunewalde01307Germany
72Seven Seas ImportsHari Kumar90 Wadhurst Rd.LondonOX15 4NBUK
74Spécialités du mondeDominique Perrier25, rue LauristonParis75016France
79Toms SpezialitätenKarin JosephsLuisenstr. 48Münster44087Germany
84Victuailles en stockMary Saveley2, rue du CommerceLyon69004France
85Vins et alcools ChevalierPaul Henriot59 rue de l’AbbayeReims51100France
86Die Wandernde KuhRita MüllerAdenauerallee 900Stuttgart70563Germany

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:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK

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

OrderID CustomerIDEmployeeIDOrderDateShipperID
103545881996-11-143
10355461996-11-151
103568661996-11-182
SELECT CustomerID as ID, CustomerName AS Customer
FROM Customers;

Result –

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

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:

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18
2Chang1124 – 12 oz bottles19
3Aniseed Syrup1212 – 550 ml bottles10
4Chef Anton’s Cajun Seasoning1248 – 6 oz jars22
5Chef Anton’s Gumbo Mix1236 boxes21.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 –

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18
2Chang1124 – 12 oz bottles19
3Aniseed Syrup1212 – 550 ml bottles10
15Genen Shouyu6224 – 250 ml bottles15.5
16Pavlova7332 – 500 g boxes17.45
21Sir Rodney’s Scones8324 pkgs. x 4 pieces10
25NuNuCa Nuß-Nougat-Creme11320 – 450 g glasses14
31Gorgonzola Telino14412 – 100 g pkgs12.5
34Sasquatch Ale16124 – 12 oz bottles14
35Steeleye Stout16124 – 12 oz bottles18
36Inlagd Sill17824 – 250 g jars19
39Chartreuse verte181750 cc per bottle18
40Boston Crab Meat19824 – 4 oz tins18.4
42Singaporean Hokkien Fried Mee20532 – 1 kg pkgs.14
44Gula Malacca20220 – 2 kg bags19.45
46Spegesild2184 – 450 g glasses12
48Chocolade22310 pkgs.12.75
49Maxilaku23324 – 50 g pkgs.20
50Valkoinen suklaa23312 – 100 g bars16.25
57Ravioli Angelo26524 – 250 g pkgs.19.5
58Escargots de Bourgogne27824 pieces13.25
66Louisiana Hot Spiced Okra2224 – 8 oz jars17
67Laughing Lumberjack Lager16124 – 12 oz bottles14
68Scottish Longbreads8310 boxes x 8 pieces12.5
70Outback Lager7124 – 355 ml bottles15
73Röd Kaviar17824 – 150 g jars15
74Longlife Tofu475 kg pkg.10
76Lakkalikööri231500 ml18
77Original Frankfurter grüne Soße12212 boxes13

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.