SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
LIKE Operator Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the persons living in a city that starts with "s" from the table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 's%'
The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
1
WHERE City LIKE '%s'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
Next, we want to select the persons living in a city that contains the pattern "tav" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%tav%'
The result-set will look like this:
P_Id LastName FirstName Address City
3 Pettersen Kari Storgt 20 Stavanger
It is also possible to select the persons living in a city that NOT contains the pattern "tav" from the "Persons" table,
by using the NOT keyword.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
SQL wildcards can be used when searching for data in a database.
SQL Wildcards
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
2
[charlist] Any single character in charlist
[^charlist] Any single character not in charlist
or
[!charlist]
SQL Wildcard Examples
We have the following "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Using the % Wildcard
Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'sa%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%nes%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3
Using the _ Wildcard
Now we want to select the persons with a first name that starts with any character, followed by "la" from the
"Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName LIKE '_la'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by
"end", followed by any character, followed by "on" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
Using the [charlist] Wildcard
Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.
The BETWEEN Operator
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.
SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
BETWEEN Operator Example
The "Persons" table:
4
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the persons with a last name alphabetically between "Hansen" and "Pettersen" from the
table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
SQL Alias
You can give a table or a column another name by using an alias. This can be a good thing to do if you have very
long or complex table names or column names.
An alias name could be anything, but usually it is short.
SQL Alias Syntax for Tables
SELECT column_name(s)
FROM table_name
AS alias_name
SQL Alias Syntax for Columns
SELECT column_name AS alias_name
FROM table_name
Alias Example
Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases
of "p" and "po" respectively.
Now we want to list all the orders that "Ola Hansen" is responsible for.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons AS p,
5
Product_Orders AS po
WHERE [Link]='Hansen' AND [Link]='Ola'
The same SELECT statement without aliases:
SELECT Product_Orders.OrderID, [Link], [Link]
FROM Persons,
Product_Orders
WHERE [Link]='Hansen' AND [Link]='Ola'
As you'll see from the two SELECT statements above; aliases can make queries easier to both write and to read.
SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship
between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value
must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the
data in every table.
Look at the "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the
same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the
persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.
Different SQL JOINs
6
Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: INNER JOIN is the same as JOIN.
SQL INNER JOIN Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons with any orders.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
7
Pettersen Kari 77895
Pettersen Kari 44678
The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in
"Persons" that do not have matches in "Orders", those rows will NOT be listed.
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in
the right table (table_name2).
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
8
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in
the right table (Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no
matches in the left table (table_name1).
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
9
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
34764
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches
in the left table (Persons).
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
SQL FULL JOIN Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT [Link], [Link], [Link]
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY [Link]
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
10
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right
table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in
"Orders" that do not have matches in "Persons", those rows will be listed as well.
11