Scribd
Upload
468 views2 pages

SQL Self Joins

self joins-sql

Uploaded by

sudhavishu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
468 views2 pages

SQL Self Joins

self joins-sql

Uploaded by

sudhavishu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

SQL SELF JOINS

1 of 2

http://www.tutorialspoint.com/cgi-bin/printpage.cgi

SQL - SELF JOINS


http://w w w .tutor i al spoi nt.c om/sql /sql -sel f -joi ns.htm

Copyright tutorialspoint.com

T h e SQL SELF JOIN is u sed t o join a t a ble t o it self a s if t h e t a ble w er e t w o t a bles,


t em por a r ily r en a m in g a t lea st on e t a ble in t h e SQL st a t em en t .

Sy ntax :
T h e ba sic sy n t a x of SELF JOIN is a s follow s:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Her e, W HERE cla u se cou ld be a n y g iv en ex pr ession ba sed on y ou r r equ ir em en t .

Ex am ple:
Con sider t h e follow in g t w o t a bles, a CUST OMERS t a ble is a s follow s:
+----+----------+-----+-----------+----------+
| ID | NAME
| AGE | ADDRESS
| SALARY
|
+----+----------+-----+-----------+----------+
| 1 | Ramesh
| 32 | Ahmedabad | 2000.00 |
| 2 | Khilan
| 25 | Delhi
| 1500.00 |
| 3 | kaushik | 23 | Kota
| 2000.00 |
| 4 | Chaitali | 25 | Mumbai
| 6500.00 |
| 5 | Hardik
| 27 | Bhopal
| 8500.00 |
| 6 | Komal
| 22 | MP
| 4500.00 |
| 7 | Muffy
| 24 | Indore
| 10000.00 |
+----+----------+-----+-----------+----------+

Now , let u s join t h is t a ble u sin g SELF JOIN a s follow s:


SQL> SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

T h is w ou ld pr odu ce t h e follow in g r esu lt :


+----+----------+---------+
| ID | NAME
| SALARY |
+----+----------+---------+
| 2 | Ramesh
| 1500.00 |
| 2 | kaushik | 1500.00 |
| 1 | Chaitali | 2000.00 |
| 2 | Chaitali | 1500.00 |

1/19/2016 12:37 PM

SQL SELF JOINS

2 of 2

http://www.tutorialspoint.com/cgi-bin/printpage.cgi

| 3 | Chaitali | 2000.00 |
| 6 | Chaitali | 4500.00 |
| 1 | Hardik
| 2000.00 |
| 2 | Hardik
| 1500.00 |
| 3 | Hardik
| 2000.00 |
| 4 | Hardik
| 6500.00 |
| 6 | Hardik
| 4500.00 |
| 1 | Komal
| 2000.00 |
| 2 | Komal
| 1500.00 |
| 3 | Komal
| 2000.00 |
| 1 | Muffy
| 2000.00 |
| 2 | Muffy
| 1500.00 |
| 3 | Muffy
| 2000.00 |
| 4 | Muffy
| 6500.00 |
| 5 | Muffy
| 8500.00 |
| 6 | Muffy
| 4500.00 |
+----+----------+---------+

1/19/2016 12:37 PM

You might also like