The PL/SQL SELF JOIN is joining a table to itself and returns rows when there is at least one match in the tables.
Phones table
| PHONE_ID | PHONE_NAME | BRAND_ID |
|---|---|---|
| 1 | Galaxy Note Edge | 1 |
| 2 | N910 Galaxy Note 4 | 1 |
| 3 | Galaxy S5 Duos 4G | 1 |
| 4 | iPhone 5S | 2 |
| 5 | iPhone 6 Plus | 2 |
| 6 | 930 Lumia | 3 |
| 7 | 830 Lumia | 3 |
Self Join example
SELECT p.phone_id, p.phone_name, p2.brand_id FROM phones p, phones p2 WHERE p.phone_id = p2.phone_id ORDER BY p.brand_id;
Output
| PHONE_ID | PHONE_NAME | BRAND_ID |
|---|---|---|
| 1 | Galaxy Note Edge | 1 |
| 2 | N910 Galaxy Note 4 | 1 |
| 3 | Galaxy S5 Duos 4G | 1 |
| 4 | iPhone 5S | 2 |
| 5 | iPhone 6 Plus | 2 |
| 6 | 930 Lumia | 3 |
| 7 | 830 Lumia | 3 |