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
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 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
Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
|
The
result-set will look like this:
LastName
|
FirstName
|
OrderNo
|
Hansen
|
Ola
|
22456
|
Hansen
|
Ola
|
24562
|
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 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
Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
|
The
result-set will look like this:
LastName
|
FirstName
|
OrderNo
|
Hansen
|
Ola
|
22456
|
Hansen
|
Ola
|
24562
|
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 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
Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
|
The
result-set will look like this:
LastName
|
FirstName
|
OrderNo
|
Hansen
|
Ola
|
22456
|
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 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
Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
|
The
result-set will look like this:
LastName
|
FirstName
|
OrderNo
|
Hansen
|
Ola
|
22456
|
Hansen
|
Ola
|
24562
|
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.
The SQL UNION Operator
The UNION
operator is used to combine the result-set of two or more SELECT statements.
Notice
that each SELECT statement within the UNION must have the same number of
columns. The columns must also have similar data types. Also, the columns in
each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
|
Note: The UNION operator selects only
distinct values by default. To allow duplicate values, use UNION ALL.
SQL UNION ALL
Syntax
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
|
PS: The column names in the result-set of a
UNION are always equal to the column names in the first SELECT statement in the
UNION.
SQL UNION Example
Look at
the following tables:
"Employees_Norway":
E_ID
|
E_Name
|
01
|
Hansen, Ola
|
02
|
Svendson, Tove
|
03
|
Svendson, Stephen
|
04
|
Pettersen, Kari
|
"Employees_USA":
E_ID
|
E_Name
|
01
|
Turner, Sally
|
02
|
Kent, Clark
|
03
|
Svendson, Stephen
|
04
|
Scott, Stephen
|
Now we
want to list all the different
employees in Norway and USA.
We use
the following SELECT statement:
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA
|
The
result-set will look like this:
E_Name
|
Hansen, Ola
|
Svendson, Tove
|
Svendson, Stephen
|
Pettersen, Kari
|
Turner, Sally
|
Kent, Clark
|
Scott, Stephen
|
Note: This command cannot be used to list
all employees in Norway and USA. In the example above we have two employees
with equal names, and only one of them will be listed. The UNION command
selects only distinct values.
SQL UNION ALL Example
Now we
want to list all employees in
Norway and USA:
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
|
Result
E_Name
|
Hansen, Ola
|
Svendson, Tove
|
Svendson, Stephen
|
Pettersen, Kari
|
Turner, Sally
|
Kent, Clark
|
Svendson, Stephen
|
Scott, Stephen
|