Outer Join in SQL and It’s Examples

Introduction to Outer Join in SQL

Outer Join in SQL is used when you want to retrieve some uncommon data from multiple tables. The Outer join is used to retrieve the information which is not common between two tables.

There are three types of outer join:-

• Left Outer join
• Right, Outer Join. 
• Full Outer Join

The Outer JOIN creates a new result table by joining column values of two tables (table1 and table2) based upon the join-word. 

Difference between inner vs outer join

• The joins of two tables returning only matched rows is called an INNER join.
• A join between two tables that returns the results of the INNER join, as well as the unmatched rows from the table, is called an outer join.
• A join between two tables that returns the results of an INNER join, as well as the results of left and right join, is a full OUTER join.

Types of Outer Join in SQL are

• Left Outer join
• Right, Outer Join. 
• Full Outer Join

• The left Outer join all the data from the left table and only matched data from the right table
• Right, Outer Join all the data from the right table and only matched data from the left table
• Full Outer Join all the data from both the table either its matched or not.

Left Outer Join

IN left outer join the SQL retrieves the matched or common rows from both tables, and also retrieves unmatched rows from the left side table.

The syntax for left outer join

select [ selecting columns]

From table1 Right OUTER JOIN table2

ON(table1.common col=table2.common col)

In this syntax, table1 is a left table.

so all the matched records will retrieve from table 1 and table 2

and all unmatched records from table 1 because we used table 1 as a left table in the left outer join.

For Example

Display first_name,department_id (employee)and department_name(department)

using left outer join?

SQL> select e.first_name,e.department_id,d.department_name
  2  from employees e LEFT OUTER JOIN departments d
  3  ON(e.department_id=d.department_id);

For Example:-

Display employee_id from employees and department_id from department table using a left outer join.

Right Outer JOIN

All the records from the right side table will get retrieved and only common records from the left side table.

The syntax for right outer join;

select [ selecting columns]

From table1 Right OUTER JOIN table2

ON(table1.common col=table2.common col)

In this syntax, table2 is the right table.

In this syntax, table2 is the right table.

so all the matched records will retrieve from table 1 and table 2

and all unmatched records from table 2 because we used table 2 as a right table in the right outer join.

For Example:-Display emp last_name,emp salary,department_name,department_id (dept)

Display emp last_name,emp salary,department_name,department_id (dept)

using right outer join.

LAST_NAME    SALARY  DEPARTMENT_NAME  DEPARTMENT_ID

King                     24000    Executive                  90

King

Here we retrieve all information from the right side table and common information from the left side table. 

Full Outer Join in SQL

IN full outer join SQL retrieved information from both the table 

not only matched but also unmatched records from left as well as the right side of the table

The syntax for right outer join;

select column_name

from table 1 full outer join table 2

on(connection);

here all the data from table 1 and table 2 will get retrieved.

For Example:-

Display

left table -> e.last_name

right table-> d.department_name,d.department_id

SQL> select e.last_name,d.department_name,d.department_id
  2  from employees e FULL OUTER JOIN departments d
  3  ON(e.department_id=d.department_id);

 
Widget not in any sidebars

Conclusion

In this blog, you will get the knowledge of types of Outer join and their uses which is help you to build a career in SQL.