You are here
Home > Sql Server > SQL JOIN (SELF JOIN)

SQL JOIN (SELF JOIN)

SQL JOIN

Self Join:

  • Self join is used to join table with itself.
  • Syntax:
  • select column_name(s)

    from table1 A1,table2 A2

    where condition;

  • Here, A1 and A2 are different alias name for the same table.
  • Database Table: Customers

Cust_id

Name Age City
1 Prince 22

Anand

2

Komal 44 Pune
3 Khusi 30

Delhi

4

Anil 45 Mumbai
5 Abhi 27

Ahmedabad

6

Juhi 35

Baroda

 

  • Here, Cust_id is primary key.
  • Example-1:
  • select x.Name as Name1, x.Age as Age1, x.City as City1,

    y.Name as Name2, y.Age as Age2, y.City as City2

    from Customers as x , Customers as y

    where x.Cust_id= y.Cust_id;

  • Output:

Name1

Age1 City1 Name2 Age2 City2
Prince 22 Anand Prince 22

Anand

Komal

44 Pune Komal 44 Pune
Khusi 30 Delhi Khusi 30

Delhi

Anil

45 Mumbai Anil 45 Mumbai
Abhi 27 Ahmedabad Abhi 27

Ahmedabad

Juhi

35 Baroda Juhi 35

Baroda

 

  • Example-2: Using join
  •  

    select x.Name as Name1, x.Age as Age1, x.City as City1,

  • y.Name as Name2, y.Age as Age2, y.City as City2

    from Customers as x

    join Customers as y

    on x.Cust_id= y.Cust_id

    order by x.City;

  • Output:

Name1

Age1 City1 Name2 Age2 City2
Abhi 27 Ahmedabad Abhi 27

Ahmedabad

Prince

22 Anand Prince 22 Anand
Juhi 35 Baroda Juhi 35

Baroda

Khusi

30 Delhi Khusi 30 Delhi
Anil 45 Mumbai Anil 45

Mumbai

Komal

44 Pune Komal 44

Pune

 

  • Database Table: Orders

Order_id

Order_no Price Cust_id
1 1111 1100

2

2

2244 227 2
3 7777 677

4

4

4466 449 4
5 8888 1700

6

 

  • Here, Order_id is primary key.
  • Example-1:
  • select x.Order_no as Order_no1, x.Price as Price1, y.Order_no as Order_no2, y.Price as Price2

    from Orders as x , Orders as y

    where x.Order_id= y.Order_id

    and x.Cust_id= y.Cust_id

    order by x.Order_no;

  • Output:

Order_no1

Price1 Order_no2 Price2
1111 1100 1111

1100

2244

227 2244 227
4466 449 4466

449

7777

677 7777 677
8888 1700 8888

1700

 

  • Example-2:
  • select x.Order_no as Order_no1, x.Price as Price1, y.Order_no as Order_no2, y.Price as Price2

    from Orders as x , Orders as y

    where x.Order_id < > y.Order_id

    and x.Cust_id=y.Cust_id

    order by x.Order_no;

  • Output:

Order_no1

Price1 Order_no2 Price2
1111 1100 2244

227

2244

227 1111 1100
4466 449 7777

677

7777

677 4466 449

 

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

 

Leave a Reply

Top