An SQL Joins are used to combine rows from two or more tables, based on a common field between them.
Lets consider two tables suppliers and orders for explaining the joins.
Suppliers:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
Orders:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2015/09/12 |
| 500126 | 10001 | 2015/09/13 |
| 500127 | 10004 | 2015/09/14 |
INNER JOIN (or sometimes called simple join or Equi join):
The INNER JOIN selects all rows from both tables as long as there is a match between the columns in both tables. The output of the join are records which match in both the suppliers and orders tables, i.e. all suppliers who have some orders:
Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;or
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;Example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;the result is:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 2015/09/12 |
| 10001 | Hewlett Packard | 2015/09/13 |
LEFT JOIN (also known as LEFT OUTER JOIN)
The LEFT JOIN returns all rows from the left table (table1), with the matching rows in the right table (table2). The result
is NULL in the right side when there is no match. This will return all rows from the suppliers table and only those rows
from the orders table where the joined fields are equal. If a supplier_id value in the suppliers table does not exist in
the orders table, all fields in the orders table will display as
Syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;Example:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;Result:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 2015/09/12 |
| 10001 | Hewlett Packard | 2015/09/13 |
| 10002 | Microsoft | (null) |
| 10003 | NVIDIA | (null) |
RIGHT JOIN (also known as RIGHT OUTER JOIN)
The RIGHT JOIN returns all rows from the right table (table2), with the matching rows in the left table (table1). The
result is NULL in the left side when there is no match. This will return all rows from the orders table and only those
rows from the suppliers table where the joined fields are equal. If a supplier_id value in the orders table does not
exist in the suppliers table, all fields in the suppliers table will display as
Syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;Example:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;Result:
| order_id | order_date | supplier_name |
|---|---|---|
| 500125 | 2015/09/12 | IBM |
| 500126 | 2015/09/13 | Hewlett Packard |
| 500127 | 2015/09/14 | (null) |
OUTER JOIN (or FULL OUTER JOIN)
The OUTER JOIN returns all rows from the left table (table1) and from the right table (table2). The OUTER JOIN keyword combines the result of both LEFT and RIGHT joins. This will return all rows from the orders table and suppliers table.
Syntax:
SELECT column_name(s)
FROM table1
OUTER JOIN table2
ON table1.column_name=table2.column_name;Example:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;Result:
| order_id | order_date | supplier_name |
|---|---|---|
| 500125 | 2015/09/12 | IBM |
| 500126 | 2015/09/13 | Hewlett Packard |
| (null) | (null) | Microsoft |
| (null) | (null) | NVIDIA |
| 500127 | 2015/09/14 | (null) |