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.