Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


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.

SQL SELF Join

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 statement :

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 explaination :

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.