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.
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.
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.
Summary
This article gives an overview of INNER JOIN and OUTER JOIN in Oracle, with examples, memory aids and a further drilldown of INNER JOIN and OUTER JOIN types.
