LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second
position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least
2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least
3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with
"o"
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
Customer Customer Customer Address City Postal Country
ID Name Name Code
1 Alfreds Maria Obere Str. 57 Berlin 12209 Germany
Futterkiste Anders
2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico
Emparedados Constitución D.F.
y helados 2222
3 Antonio Antonio Mataderos México 05023 Mexico
Moreno Moreno 2312 D.F.
Taquería
4 Around the Thomas 120 Hanover London WA1 UK
Horn Hardy Sq. 1DP
5 Berglunds Christina Berguvsvägen Luleå S-958 Sweden
snabbköp Berglund 8 22
6 Blauer See Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
Delikatessen
7 Blondel père Frédérique 24, place Strasbourg 67000 France
et fils Citeaux Kléber
8 Bólido Martín C/ Araquil, Madrid 28023 Spain
Comidas Sommer 67
preparadas
9 Bon app' Laurence 12, rue des Marseille 13008 France
Lebihans Bouchers
Using the % Wildcard
The following SQL statement selects all customers with a City starting with "ber":
Example
SELECT * FROM Customers
WHERE City LIKE 'ber%';
The following SQL statement selects all customers with a City containing the pattern "es":
Example
SELECT * FROM Customers
WHERE City LIKE '%es%';
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character, followed by
"ondon":
Example
SELECT * FROM Customers
WHERE City LIKE '_ondon';
The following SQL statement selects all customers with a City starting with "L", followed by any
character, followed by "n", followed by any character, followed by "on":
Example
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
Using the [charlist] Wildcard
The following SQL statement selects all customers with a City starting with "b", "s", or "p":
Example
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
The following SQL statement selects all customers with a City starting with "a", "b", or "c":
Example
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
Using the [!charlist] Wildcard
The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":
Example
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
Or:
Example
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
SQL IN Operator
The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
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);
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
Customer Customer Customer Address City Postal Country
ID Name Name Code
1 Alfreds Maria Obere Str. 57 Berlin 12209 Germany
Futterkiste Anders
2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico
Emparedados Constitución D.F.
y helados 2222
3 Antonio Antonio Mataderos México 05023 Mexico
Moreno Moreno 2312 D.F.
Taquería
4 Around the Thomas 120 Hanover London WA1 UK
Horn Hardy Sq. 1DP
5 Berglunds Christina Berguvsvägen Luleå S-958 Sweden
snabbköp Berglund 8 22
6 Blauer See Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
Delikatessen
7 Blondel père Frédérique 24, place Strasbourg 67000 France
et fils Citeaux Kléber
8 Bólido Martín C/ Araquil, Madrid 28023 Spain
Comidas Sommer 67
preparadas
9 Bon app' Laurence 12, rue des Marseille 13008 France
Lebihans Bouchers
IN Operator Examples
The following SQL statement selects all customers that are located in "Germany", "France" or "UK":
Example
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are NOT located in "Germany", "France" or
"UK":
Example
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are from the same countries as the suppliers:
Example
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
SQL BETWEEN Operator
The SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
Product Product Supplier Category Unit Price
ID ID
Name ID
1 Chais 1 1 18
10 boxes x 20 bags
2 Chang 1 1 19
24 - 12 oz bottles
3 1 2 10
Aniseed Syrup 12 - 550 ml bottles
4 1 2 22
Chef Anton's Cajun 48 - 6 oz jars
Seasoning
5 1 2 21.35
Chef Anton's 36 boxes
Gumbo Mix
BETWEEN Example
The following SQL statement selects all products with a price between 10 and 20:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN with IN Example
The following SQL statement selects all products with a price between 10 and 20. In addition; do not
show products with a CategoryID of 1,2, or 3:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and
Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and
Chef Anton's Cajun Seasoning:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName not between Carnarvon Tigers and
Mozzarella di Giovanni:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
Sample Table
Below is a selection from the "Orders" table in the Northwind sample database:
OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2
BETWEEN Dates Example
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-
1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
OR:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
SQL Aliases
SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
Customer Customer Customer Address City Postal Country
ID Name Name Code
1 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico
Emparedados Constitución D.F.
y helados 2222
2 Antonio Antonio Mataderos México 05023 Mexico
Moreno Moreno 2312 D.F.
Taquería
3 Around the Thomas 120 Hanover London WA1 UK
Horn Hardy Sq. 1DP
And a selection from the "Orders" table:
Order ID Customer ID Employee ID Order Date Shipper ID
10354 54 8 1996-11-14 3
10355 8 6 1996-11-15 1
10356 86 6 1996-11-18 2
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for the
CustomerName column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
The following SQL statement creates two aliases, one for the CustomerName column and one for the
ContactName column. Note: It requires double quotation marks or square brackets if the alias name
contains spaces:
Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address,
PostalCode, City and Country):
Example
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
Note: To get the SQL statement above to work in MySQL use the following:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
Note: To get the SQL statement above to work in Oracle use the following:
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
FROM Customers;
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the
Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o"
respectively (Here we use aliases to make the SQL shorter):
Example
SELECT [Link], [Link], [Link]
FROM Customers AS c, Orders AS o
WHERE [Link]='Around the Horn' AND [Link]=[Link];
The following SQL statement is the same as above, but without aliases:
Example
SELECT [Link], [Link], [Link]
FROM Customers, Orders
WHERE [Link]='Around the
Horn' AND [Link]=[Link];
Aliases can be useful when:
There are more than one table involved in a query
Functions are used in the query
Column names are big or not very readable
Two or more columns are combined together
SQL Joins
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between
them.
Let's look at a selection from the "Orders" table:
Order ID Customer ID Order Date
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20
Then, look at a selection from the "Customers" table:
CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y Ana Trujillo Mexico
helados
3 Antonio Moreno Taquería Antonio Moreno Mexico
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers"
table. The relationship between the two tables above is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records
that have matching values in both tables:
Example
SELECT [Link], [Link], [Link]
FROM Orders
INNER JOIN Customers ON [Link]=[Link];
and it will produce something like this:
Order ID Customer Name Order Date
10308 Ana Trujillo Emparedados y 9/18/1996
helados
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the
right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from
the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Orders" table:
Order ID Customer ID Employee ID Order Date Shipper ID
10308 2 7 1996-09-18 3
10309 37 3 1996-09-19 1
10310 77 8 1996-09-20 2