Overview
In RDBMS, tables are related to each other based on some relationship between them and JOIN is the way to fetch data from those related tables at once.
Joining 2 tables in SQL is pretty simple. In the case of 3 or more tables, join is performed on first 2 tables producing an intermediate table that gets further JOINed with next table and so on.
Tables can be joined in different ways which can fetch different set of data.
There are mainly 6 types of join as follows.
Inner Join
This type of JOIN returns only matching data from both the tables based on the provided condition.
Keyword INNER JOIN or just JOIN is used to refer to this join.
Syntax
SELECT table1.column1,
table1.column2,
table2.column1,
table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_columnDetailed example here SQL Inner Join.
Left Outer Join
This JOIN returns all records from the left table and only matching records from the right table.
Keyword LEFT JOIN or LEFT OUTER JOIN is used to refer to this join.
Syntax
SELECT table1.column1,
table1.column2,
table2.column1,
table2.column2
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_columnDetailed example here LEFT OUTER JOIN.
Right Outer Join
Returns all records from the right table and only matching records from the left table.
Keyword RIGHT JOIN or RIGHT OUTER JOIN is used to make this join.
Syntax
SELECT table1.column1,
table1.column2,
table2.column1,
table2.column2
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_columnDetailed example here RIGHT OUTER JOIN.
Full Outer Join
Returns all records from both the tables. Columns where data is not matched, NULL is returned.
Keyword FULL JOIN or FULL OUTER JOIN is used to make this join.
Note: Not all the databases support this kind of join. However, there are ways to work around this join.
Syntax
SELECT table1.column1,
table1.column2,
table2.column1,
table2.column2
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_columnCross Join
This join produces mathematical Cartesian product of both the tables i.e. each row from the right table is returned against each row from the left table.
Keyword CROSS JOIN is used to make this join. There is no join condition in this join type.
Syntax
SELECT table1.column1,
table1.column2,
table2.column1,
table2.column2
FROM table1
CROSS JOIN table2Detailed example here SQL Cross Join.
Self Join
As the name says, a table is joined to itself to make SELF JOIN. No special keyword is used. This is a regular join; the only difference is that 2 replicas (aliases) of the same table are used to make this join.
Syntax
SELECT table1.column1,
table2.column2
FROM table table1, table table2
WHERE <condition>Detailed example here SQL SELF JOIN.
Example tables
Following are the 3 tables that we are going to use in above join types.
Customer, a customer table having 4 records.
+-------------+---------+------------------+
| customer_id | name | email |
+-------------+---------+------------------+
| 100 | Michael | michael@test.com |
| 101 | Robert | robert@test.com |
| 102 | John | john@test.com |
| 103 | Grey | grey@test.com |
+-------------+---------+------------------+
4 rows in set (0.00 sec)Orders, keeps orders from a customer. A customer may have 0, 1 or more orders.
+----------+-------------+---------------------+
| order_id | customer_id | order_date |
+----------+-------------+---------------------+
| 10 | 100 | 2020-03-01 21:37:55 |
| 11 | 100 | 2020-03-01 21:37:55 |
| 12 | 101 | 2020-03-01 21:37:55 |
| 13 | 103 | 2020-03-01 21:37:55 |
| 14 | 106 | 2020-03-01 21:37:55 |
| 15 | 107 | 2020-03-01 21:37:55 |
| 16 | 108 | 2020-03-01 21:37:55 |
+----------+-------------+---------------------+
7 rows in set (0.00 sec)Invoice, keeps invoice for an order. An order may or may not have an invoice.
+------------+----------+---------------------+
| invoice_id | order_id | invoice_date |
+------------+----------+---------------------+
| 1 | 11 | 2020-03-01 21:37:55 |
| 2 | 12 | 2020-03-01 21:37:55 |
| 3 | 13 | 2020-03-01 21:37:55 |
| 4 | 14 | 2020-03-01 21:37:55 |
| 5 | 15 | 2020-03-01 21:37:55 |
| 6 | 16 | 2020-03-01 21:37:55 |
| 7 | 17 | 2020-03-01 21:37:55 |
| 8 | 18 | 2020-03-01 21:37:55 |
+------------+----------+---------------------+
8 rows in set (0.00 sec)