SQL SELF JOIN – Examples and Explanation

SQL
Jul 29 2023
SQL SELF JOIN- This type of join is used when a table needs to be compared with itself.

Table of Contents

Overview SQL SELF JOIN

In SQL SELF JOIN, as the name says, a table is joined to itself by using aliases. We can use 2 or more aliases of the same table to make this join. Aliases are simply replicas of the same table. This type of join is used when a table needs to be compared with itself.

There is no special keyword for this join, just a regular WHERE clause which specifies the join condition.

Example SQL SELF JOIN

Consider the following table called orders from previous post SQL JOIN Explained with examples.

+----------+-------------+---------------------+
| 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 |
+----------+-------------+---------------------+

Let’s find out orders where customer_id is less the other customer_id.

SELECT a.*, 
       b.customer_id 
FROM   orders a, 
       orders b 
WHERE  a.customer_id < b.customer_id;

Result

+----------+-------------+---------------------+-------------+
| order_id | customer_id | order_date          | customer_id |
+----------+-------------+---------------------+-------------+
|       11 |         100 | 2020-03-01 21:37:55 |         101 |
|       10 |         100 | 2020-03-01 21:37:55 |         101 |
|       12 |         101 | 2020-03-01 21:37:55 |         103 |
|       11 |         100 | 2020-03-01 21:37:55 |         103 |
|       10 |         100 | 2020-03-01 21:37:55 |         103 |
|       13 |         103 | 2020-03-01 21:37:55 |         106 |
|       12 |         101 | 2020-03-01 21:37:55 |         106 |
|       11 |         100 | 2020-03-01 21:37:55 |         106 |
|       10 |         100 | 2020-03-01 21:37:55 |         106 |
|       14 |         106 | 2020-03-01 21:37:55 |         107 |
|       13 |         103 | 2020-03-01 21:37:55 |         107 |
|       12 |         101 | 2020-03-01 21:37:55 |         107 |
|       11 |         100 | 2020-03-01 21:37:55 |         107 |
|       10 |         100 | 2020-03-01 21:37:55 |         107 |
|       15 |         107 | 2020-03-01 21:37:55 |         108 |
|       14 |         106 | 2020-03-01 21:37:55 |         108 |
|       13 |         103 | 2020-03-01 21:37:55 |         108 |
|       12 |         101 | 2020-03-01 21:37:55 |         108 |
|       11 |         100 | 2020-03-01 21:37:55 |         108 |
|       10 |         100 | 2020-03-01 21:37:55 |         108 |
+----------+-------------+---------------------+-------------+

As you can see, we have joined 2 replicas of the same table based on some column and fetched data from both replicas.