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

SQL JOIN (OUTER JOIN)

SQL JOIN

Outer Join:

  • Outer join returns all rows from one table and matching rows from second table.
  • There are three types of Outer join:
  • 1) Left Outer Join

    2) Right Outer Join

    3) Full Outer Join

  • Syntax:
  • select column_name(s)

    from table_name1

    left | right | full [outer] join table_name2

    on table_name1. column_name= table_name2. column_name

Left Outer Join or Left Join:

  • Left join returns all data from the left table and matched data from right table.
  • If there is no match, the result is NULL from right side.
  • 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.
  • 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, Cust_id is foreign key and Order_id is primary key.
  • Example:
  • select C.Name, C.Age, C.City,

    O.Order_id, O.Order_no, O.Price

    from Customers as C

    left join Orders as O

    on C.Cust_id=O.Cust_id;

  • Output:

Name

Age City Order_id Order_no Price
Prince 22 Anand NULL NULL

NULL

Komal

44 Pune 1 1111 1100
Komal 44 Pune 2 2244

227

Khusi

30 Delhi NULL NULL NULL
Anil 45 Mumbai 3 7777

677

Anil

45 Mumbai 4 4466 449
Abhi 27 Ahmedabad NULL NULL

NULL

Juhi

35 Baroda 5 8888

1700

 

Right Outer Join or Right Join:

  • Right join returns all data from the right table and matched data from left table.
  • If there is no match, the result is NULL from left side.
  • 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.
  • 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, Cust_id is foreign key and Order_id is primary key.
  • Example:
  • select C.Name, C.Age, C.City,

    O.Order_id, O.Order_no, O.Price

    from Customers as C

    right join Orders as O

    on C.Cust_id=O.Cust_id;

  • Output:

Name

Age City Order_id Order_no Price
Komal 44 Pune 1 1111

1100

Komal

44 Pune 2 2244 227
Anil 45 Mumbai 3 7777

677

Anil

45 Mumbai 4 4466 449
Juhi 35 Baroda 5 8888

1700

 

Full Outer Join or Full Join:

  • Full Outer join returns all data from both tables.
  • Example: Here, we can use above database tables Customers and Orders to perform below query.
  •  

    select C.Name, C.Age, C.City,

    O.Order_id, O.Order_no, O.Price

    from Customers as C

    full join Orders as O

    on C.Cust_id=O.Cust_id;

  • Output:

Name

Age City Order_id Order_no Price
Prince 22 Anand NULL NULL

NULL

Komal

44 Pune 1 1111 1100
Komal 44 Pune 2 2244

227

Khusi

30 Delhi NULL NULL NULL
Anil 45 Mumbai 3 7777

677

Anil

45 Mumbai 4 4466 449
Abhi 27 Ahmedabad NULL NULL

NULL

Juhi

35 Baroda 5 8888

1700

 

 

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

Learn more about DBA at TechNet Consultancy

 

Leave a Reply

Top