SQL SELF JOIN

SQL Self JOIN joining same table to itself. SQL Self JOIN possible only when table stored records are in hierarchical relationship between them.

You must use table alias name otherwise you can't distinguish columns name (referenced of which table's).

Logically assume that same table have two different copies but not actually different copies.

Example Table

Considering following orders table is our example table.

SQL> CREATE TABLE orders(
  order_id number(3),
  product_name VARCHAR(25),
  customer_name VARCHAR(25),
  order_amount number(5)
);

INSERT INTO orders VALUES (1,'Nokia Mobile', 'Opal Kole', 290);
INSERT INTO orders VALUES (2,'Flip Cover', 'Max Miller', 15);
INSERT INTO orders VALUES (3,'Handsfree', 'Beccaa Moss', 7);
INSERT INTO orders VALUES (4,'Canon Camera', 'Paul Singh', 2200);
INSERT INTO orders VALUES (5,'Playstation', 'Max Miller', 70);
INSERT INTO orders VALUES (6,'Sandwich Maker', 'Beccaa Moss', 28);
INSERT INTO orders VALUES (7,'Pen Drive 16GB', 'Opal Kole', 12);
INSERT INTO orders VALUES (8,'Headphone', 'Reed Koch', 38);
INSERT INTO orders VALUES (9,'HP Laptop i5', 'Reed Koch', 1300);
INSERT INTO orders VALUES (10,'Titanic Bluray Disk', 'Beccaa Moss', 78);

SELECT * FROM orders;

ORDER_ID PRODUCT_NAME       CUSTOMER_NAME          ORDER_AMOUNT
-------- ------------------ ---------------------- --------------
       1 Nokia Mobile       Opal Kole             290
       2 Flip Cover         Max Miller            15
       3 Handsfree          Beccaa Moss           7
       4 Canon Camera       Paul Singh            2200
       5 Playstation        Max Miller            70
       6 Sandwich Maker     Beccaa Moss           28
       7 Pen Drive 16GB     Opal Kole             12
       8 Headphone          Reed Koch             38
       9 HP Laptop i5       Reed Koch             1300
      10 Titanic Bluray DiskBeccaa Moss           78

10 rows selected.

Run it... »

SQL SELF Join Example

SQL> SELECT A.order_id, A.product_name, 
             B.order_id, B.product_name, B.customer_name
    FROM orders A 
    INNER JOIN orders B
    ON A.customer_name = B.customer_name
    WHERE A.order_id < B.order_id;

    ORDER_ID PRODUCT_NAME       CUSTOMER_NAME
------------ ------------------ ----------------------
2   Flip Cover  Max Miller
3   Handsfree   Beccaa Moss
1   Nokia Mobile    Opal Kole
8   Headphone   Reed Koch
6   Sandwich Maker  Beccaa Moss
3   Handsfree   Beccaa Moss

Run it... »

SQL SELF Join Example Explanation

Above example we are use A and B table alias name.

Inner Join to join predicate condition (A.customer_name = B.customer_name) customer_name only those whose are in same customer_name in Table A or Table B. It's mean cross customer_name are eliminate.

And last WHERE clause A.order_id < B.order_id means eliminate pairings where order_id Table A is not less than to order_id of Table B.