SQL JOIN Explained with Examples

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_column

Detailed 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_column

Detailed 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_column

Detailed 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_column

Cross 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 table2

Detailed 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)