SQL Outer Join

Introduction to SQL Outer Join

In SQL, outer join refers to the fact that contents that are common to both the tables along with those which are uncommon are extracted as final output. Such a join between two or more tables facilitating data extraction in such a fashion is referred to as outer join.

Types of Outer Join

In SQL we have three types of outer joins.

These are:

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

Full Outer Join basically works like a set in mathematics. The following section throws more light on the concept of SQL outer join.

Syntax

The syntax of a left outer join, right outer join, and full outer join are as follows.

1. Left outer join

Syntax:

SELECT table1.column_a, table1.column_b…., table2.column_a, table2.column_b,….
FROM
table1 LEFT JOIN table2
ON
table1.common_field = table2.common_field

2. Right outer join

Syntax:

SELECT table1.column_a, table1.column_b…., table2.column_a, table2.column_b,….
FROM
Table1 RIGHT JOIN table2
ON
table1.common_field = table2.common_field

3. Full Outer Join

Syntax:

SELECT table1.column_a, table1.column_b…., table2.column_a, table2.column_b,….
FROM
table1 LEFT JOIN table2
ON
table1.common_field = table2.common_field
UNION
SELECT table1.column_a, table1.column_b…., table2.column_a, table2.column_b,….
FROM
Table1 RIGHT JOIN table2
ON
table1.common_field = table2.common_field

How SQL Outer Join Work?

In order to understand the working of outer joins, we must first understand what table is specified at what position. Like in the left outer join, the output is determined by the position of the table on the left-hand side and in case of a right outer join, the output is determined by the position of the table on the right-hand side.

Now, let’s go through the following examples so as to understand how exactly the joins work.

We have two tables table(a) and table(b) as shown below.

Let’s have a look at both these tables:

Here, one field is common in the two tables. It is named as field_A in the first table, while it has been named as field_1 in the second table. We will demonstrate the working of left, right and full outer joins using the above two tables.

Let’s see what output do we get, when we perform the left outer join over the common field. When left outer join is applied over the two tables, keeping table(a) as the left table then all the contents from the left table are returned along with only those contents from the right table which are present in both the tables. So, the left table gets the precedence. The output table would look like as shown below.

We can see that for certain entries we have got NULL value. This is due to the fact that those particular IDs are not present in table(b).

Now, let’s see what happens when we perform the right outer join. In this case, the right table gets the precedence. The output table would look as follows.

In case of full outer join contents from both tables are brought together. The output table would look as follows.

Examples of SQL Outer Join

We’ll now implement outer joins in SQL. For the illustrations, we are using two tables, viz. employees and salaries. emp_no is the common field in the two tables. Let’s implement the joins and see what output the query returns in each situation.

1. Left Outer Join

Code:

select e.emp_no 'Employee Number', e.first_name 'First Name', e.last_name 'Last Name', s.salary 'Salary'
from employees e
LEFT JOIN salaries s
on
e.emp_no = s.emp_no

Output:

We got NULL in the Salary column, as salaries for those employees might have still not been updated in the salaries table.

2. Right Outer Join

Code:

select e.emp_no 'Employee Number', e.first_name 'First Name', e.last_name 'Last Name', s.salary 'Salary'
from employees e
RIGHT JOIN salaries s
on
e.emp_no = s.emp_no;

Output:

Observe carefully, the difference between the two outputs. It will give an idea as to how left and right outer join works.

3. Full Outer Join

Code:

select e.emp_no 'Employee Number', e.first_name 'First Name', e.last_name 'Last Name', s.salary 'Salary'
from employees e
LEFT JOIN salaries s
on
e.emp_no = s.emp_no
UNION
select e.emp_no 'Employee Number', e.first_name 'First Name', e.last_name 'Last Name', s.salary 'Salary'
from employees e
RIGHT JOIN salaries s
on
e.emp_no = s.emp_no;

Output:

Conclusion

Join is a very useful concept in SQL. The concept allows regulation of the extraction of data from multiple tables as required. The usefulness of the concept is quite evident in situations that involve the vast number of records from which particular records have to be extracted.

Recommended Articles

This is a guide to SQL Outer Join. Here we discuss the Types of Outer Join in SQL and how it works along with respective examples. You may also have a look at the following articles to learn more –

1. LIKE Query in SQL
2. IF ELSE Statement in SQL
3. SQL Delete Join
4. OUTER Join in Oracle