EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL Outer Join
 

SQL Outer Join

Priya Pedamkar
Article byPriya Pedamkar

Updated July 5, 2023

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 the final output. Such a join between two or more tables facilitating data extraction in such a fashion is referred to as an outer join.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 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?

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 the case of a right outer join, the output is determined by the position of the table on the right-hand side.

‘Let’s go through the following examples to understand how exactly the joins work.

As shown below, we have two tables, table (a) and table (b).

Let’s have a look at both these tables:

how does it work

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. Using the above two tables, we will demonstrate the working of the left, right, and full outer joins.

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

SQL Outer join table 1

We can see that we have a NULL value for certain entries. This is because those particular IDs are not present in table (b).

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.

SQL Outer join table 2

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

Outer table 3

Examples of SQL Outer Join

We’ll now implement outer joins in SQL. For the illustrations, we use two tables: employees and salaries. emp_no is the common field in the two tables. Let’s implement the joins and see the query’s output 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:

left Outer table

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:

Employee Table 2.1

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

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:

Employee Table

Conclusion

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

Recommended Articles

We hope that this EDUCBA information on “SQL Outer Join” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW