You are here

SQL:OUTER JOIN

outer join

OUTER JOIN

In this post we will discuss about sql outer join. This type of joins is used in situations where we have to preserve all the data of one table.

  • Outer joins are similar to inner joins, but give a more flexibility when selecting data from related tables.
  • This joins preserve all rows from one or both sides of the join, designed with the left, right, full keyword.
  • There are three types of joins:
  1. Left outer join
  2. Right outer join
  3. Full outer join

Left outer join:

  • In left join all rows are preserve from the left table and match rows from right table.
  • left join returns rows from the left table that have no matches in the right table, with NULL’s used as placeholder in the right side.
  • Outer keyword is not mandatory in outer join.

syntax:

Select col1, col2, ….., coln from <table1>

LEFT [OUTER]  Join <table2>

ON table1.col=table2.col ;

Right outer join:

  • In right join all rows are preserve from the right table and match rows from the left table.
  • Right join returns rows from the right table that have no matching rows from the left table, with NULL’s used as placeholder in the left table.

syntax:

Select col1, col2, ….., coln from <table1>

RIGHT [OUTER]  Join <table2>

ON table1.col=table2.col ;

Full outer join:

  • In full join all rows preserve from both the table, whether they match or not.
  • Full join returns the matched rows from the inner join; plus rows from the left join that don’t have matches in the right, with NULL’s used as placeholders in the right; plus rows from the right that don’t have matches in the left, with NULL’s used as placeholders in the left.

syntax:

Select col1, col2, ….., coln from <table1>

FULL [OUTER]  Join <table2>

ON table1.col=table2.col ;

Example:

Table: persons(p_id is primary key)

p_id

Firstname

lastname

city

1

Amisha

patel

Anand

2

Bina

patel

Baroda

3

Tina Doyle

v.v.nagar

4

Riya patel

Anand

Table: orders(o_id is primary key and p_id is foreign key)

o_id

orderno

p_id

1

121 3

2

122 3
2 123

1

4 124

NULL

Left outer join:

Retrieve all person name and their order:In this query we have to use left join because all person name is required whether they order item or not.
With use of outer keyword:
select p.firstname, p.lastname, od.orderno from persons as p left outer join orders as od on p.p_id=od.p_id;
Without use of outer keyword:
select p.firstname, p.lastname, od.orderno from persons as p left join orders as od on p.p_id=od.p_id;

output:

firstname

lastname

orderno

Amisha

Patel 123
Bina Patel

NULL

Tina

Doyle 121

Tina

Doyle 122
Riya patel

NULL

Right outer join:

Retrieve all order no’s and order made by person name: In this query we have to use right join because
With use of outer keyword:
select p.firstname, p.lastname, od.orderno from persons as p Right outer join orders as od on p.p_id=od.p_id;
Without use of outer keyword:
select p.firstname, p.lastname, od.orderno from persons as p Right join orders as od on p.p_id=od.p_id;

output:

firstname

lastname

orderno

Tina

Doyle 121
Tina Doyle

122

Amisha

patel 123
NULL NULL

124

Full outer join:

Retrieve all order no’s and all person name whether they place order or not: In this query we have to use full join because
With use of outer keyword:
select p.p_id, p.firstname, p.lastname, od.orderno from persons as p Full outer join orders as od on p.p_id=od.p_id;
Without use of outer keyword:
select p.p_id, p.firstname, p.lastname, od.orderno from persons as p Full join orders as od on p.p_id=od.p_id;
output:

p_id

firstname lastname orderno

1

Amisha patel 123
2 Bina patel

NULL

3

Tina Doyle

121

3 Tina Doyle

122

4

Riya patel NULL
NULL NULL NULL

124

Download Example [media-downloader media_id=”266″ texts=”Download Code”]

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy .

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top