INNER JOIN and OUTER JOIN Explained

February 10, 2017

in FAQ, Joins, Keywords, SQL

Joins

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.

Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Case Study: EMP and PROJECT tables

Let’s take two tables – EMP and PROJECT – for our example.

EMP stores employee data (empid, empname)
PROJECT stores project data (projectid, empid of the employee assigned to the project)

Every project might not have an assigned employee, and every employee might not be allocated to a project.

Data in EMP:

SQL> select *
  2  from emp;

EMPID EMPNAME
----- -------
100   Hugo
101   Bruce
102   Ethan
103   Alfred
104   Ellen

Data in PROJECT:

SQL> select *
  2  from project;

PROJECTID  EMPID
---------- -----
P1         102
P2         
P3         101

Column EMPID is the common link between the two tables. What do you get when you make a simple join between EMP and PROJECT?

SQL> select e.empid
  2       , e.empname
  3       , p.projectid
  4  from emp e
  5     , project p
  6  where
  7     e.empid = p.empid;

EMPID EMPNAME PROJECTID
----- ------- ----------
101   Bruce   P3
102   Ethan   P1

What we just wrote above is an INNER JOIN.

INNER JOINs: Deep Dive

An INNER JOIN creates a new result table by combining column values of the joined tables, based upon the join condition.

In the above example, we made an inner join between EMP and PROJECT tables based upon the condition that empid in both tables is the same.

You can see that the INNER JOIN result table excludes:

  • records from EMP table that are not assigned to projects
  • records from PROJECT table that are not linked to an employee in EMP

Scroll up to take another good look at the SQL.

Why no INNER JOIN keyword?

You may well ask.

That’s because it is an implicit INNER JOIN.

 -- Implicit INNER JOIN
select e.empid
     , e.empname
     , p.projectid
from emp e
   , project p
where 
   e.empid = p.empid; 

The implicit join notation simply lists the tables for joining in the FROM clause of the SQL using commas to separate them.

The other – and recommended option – is to use the explicit join notation.

Explicit INNER JOIN

The explicit INNER JOIN syntax uses the "inner join" keyword in the FROM clause of the SQL, with an ON keyword to specify the join condition:

select <columns>
from <table1>
inner join <table2>
on
  <join condition>

In case of EMP and PROJECT tables, the inner join SQL:

-- Explicit INNER JOIN
select e.empid
     , e.empname
     , p.projectid
from emp e
   inner join 
     project p
on
   e.empid = p.empid;

When run:

SQL> -- Explicit INNER JOIN
SQL> select e.empid
  2       , e.empname
  3       , p.projectid
  4  from emp e
  5     inner join
  6       project p
  7  on
  8     e.empid = p.empid;

EMPID EMPNAME PROJECTID
----- ------- ----------
101   Bruce   P3
102   Ethan   P1

Explicit join notation is the recommended practice, though implicit join notation produces the same result.

The need for OUTER JOINs

The result of an INNER JOIN makes sense when the tables in the query are of equal importance.

As in life, so in SQL – sometimes, one entity is more important than the other.

You might want all details from the *main* table, and associated data (if available) from the *supplementary* table.

For example, what if you want to list all employee details, with projects if assigned?

In this case
– EMP table is the *main* table, and
– PROJECT table is the *supplementary* table

You can get this result easily using an OUTER JOIN between the tables.

To select all records from one table with associated records from another table, use an OUTER JOIN between the two tables.

OUTER JOIN syntax: LEFT and RIGHT

The outer join syntax comes with a "direction" prefix – there are two kinds:

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN

So, which one suits our case: LEFT OUTER JOIN or RIGHT OUTER JOIN?

Here’s a tip:

Read the direction prefix as a signpost towards the *main* table.

In the FROM clause,
if the *main* table is written to the left of the *supplementary* table, use a left outer join;
if the *main* table is written to the right of the *supplementary* table, use a right outer join.

LEFT OUTER JOIN and RIGHT OUTER JOIN

These two SQL structures are equivalent:

select <columns>
from <main table>
left outer join <supplementary table>
on
  <join condition>

select <columns>
from <supplementary table>
right outer join <main table>
on
  <join condition>

Style 1: LEFT OUTER JOIN

In a LEFT OUTER JOIN, to treat EMP as the *main* table and PROJECT as the *supplementary* table, write EMP to the left of PROJECT in the FROM clause.

The SQL:

-- Style 1: LEFT OUTER JOIN
-- Select ALL employees, with
-- assigned projects if available
select e.empid
     , e.empname
     , p.projectid
from emp e
left outer join project p
on 
   e.empid = p.empid;

When run:

SQL> -- Style 1: LEFT OUTER JOIN
SQL> -- Select ALL employees, with
SQL> -- assigned projects if available
SQL> select e.empid
  2       , e.empname
  3       , p.projectid
  4  from emp e
  5  left outer join project p
  6  on
  7     e.empid = p.empid;

EMPID EMPNAME PROJECTID
----- ------- ----------
102   Ethan   P1
101   Bruce   P3
103   Alfred
104   Ellen
100   Hugo

Style 2: RIGHT OUTER JOIN

In a RIGHT OUTER JOIN, to treat EMP as the *main* table and PROJECT as the *supplementary* table, write EMP to the right of PROJECT in the FROM clause.

The SQL:

-- Style 2: RIGHT OUTER JOIN
-- Select ALL employees, with
-- assigned projects if available
select e.empid
     , e.empname
     , p.projectid
from project p
right outer join emp e
on 
   e.empid = p.empid;

When run:

SQL> -- Style 2: RIGHT OUTER JOIN
SQL> -- Select ALL employees, with
SQL> -- assigned projects if available
SQL> select e.empid
  2       , e.empname
  3       , p.projectid
  4  from project p
  5  right outer join emp e
  6  on
  7     e.empid = p.empid;

EMPID EMPNAME PROJECTID
----- ------- ----------
102   Ethan   P1
101   Bruce   P3
103   Alfred
104   Ellen
100   Hugo

Exercise for you: What result do you get when you swap the order of EMP and PROJECT in the RIGHT OUTER JOIN above? Try it out.

OUTER JOIN alternate syntax using (+)

Oracle has a shorthand alternate syntax for outer joins, one that needs no explicit OUTER JOIN keyword. In the WHERE clause, just add (+) next to the columns of the *supplementary* table.

-- OUTER JOIN with (+) notation
-- Select ALL employees, with
-- assigned projects if available
select e.empid
     , e.empname
     , p.projectid
from emp e
   , project p
where 
   e.empid = p.empid (+);   

When run:

SQL> -- OUTER JOIN with (+) notation
SQL> -- Select ALL employees, with
SQL> -- assigned projects if available
SQL> select e.empid
  2       , e.empname
  3       , p.projectid
  4  from emp e
  5     , project p
  6  where
  7     e.empid = p.empid (+);

EMPID EMPNAME PROJECTID
----- ------- ----------
102   Ethan   P1
101   Bruce   P3
103   Alfred
104   Ellen
100   Hugo

Memory aid: where to place the (+)

Treat the (+) like a weight added to balance the scales.

OUTER JOIN Plus Notation

The *main* table is weightier than the *supplementary* table – so the (+) should be put next to the columns of the supplementary table in order to balance the two.

Which is better: FROM clause OUTER JOIN vs (+)

FROM clause OUTER JOIN syntax is more flexible and also compatible with the outer join syntax of other databases.

Oracle recommends FROM clause OUTER JOIN syntax in its documentation, and lists the rules and restrictions on the Oracle join operator (+). Check this link for details: Oracle SQL Reference – Joins.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: