Categories
database inner-join join outer-join sql

What is the difference between “INNER JOIN” and “OUTER JOIN”?

5095

Also, how do LEFT JOIN, RIGHT JOIN, and FULL JOIN fit in?

4

  • 86

    Of the answers & comments & their references below only one actually explains how Venn diagrams represent the operators: The circle intersection area represents the set of rows in A JOIN B. The area unique to each circle represents the set of rows you get by taking its table’s rows that don’t participate in A JOIN B and adding the columns unique to the other table all set to NULL. (And most give a vague bogus correspondence of the circles to A and B.)

    – philipxy

    Oct 18, 2014 at 20:24


  • 21

    @DanteTheSmith No, that suffers from the same problems as the diagrams here. See my comment above re the question & below re that very blog post: “Jeff repudiates his blog a few pages down in the comments”. Venn diagrams show elements in sets. Just try to identify exactly what the sets are and what the elements are in these diagrams. The sets aren’t the tables and the elements aren’t their rows. Also any two tables can be joined, so PKs & FKs are irrelvant. All bogus. You are doing just what thousands of others have done–got a vague impression you (wrongly) assume makes sense.

    – philipxy

    May 18, 2017 at 5:57


  • 2

    My preceding comment is about a confused repudiated Jeff Atwood blog post.

    – philipxy

    Jan 23, 2021 at 23:15


  • 2

    My 1st comment’s link is external, but i.stack.imgur.com has permanent copies of its illustrations of output (not input) for inner, left & full joins (in green).

    – philipxy

    Feb 27, 2021 at 4:37

6560

Assuming you’re joining on columns with no duplicates, which is a very common case:

  • An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

  • An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

13

  • 64

    It would be good to augment the example by adding another row in table B with value 4. This will show that inner joins need not be on equal no of rows.

    – softveda

    Aug 30, 2009 at 12:59

  • 521

    An excellent explanation, however this statement: An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union. isn’t phrased accurately. An outer join will give the results of A intersect B in addition to one of the following: all of A (left join), all of B (right join) or all of A and all of B (full join). Only this last scenario is really A union B. Still, a well written explanation.

    – Thomas

    May 3, 2011 at 19:57

  • 10

    @Ameer, Thanks. Join does not guarantee an order, you would need to add an ORDER BY clause.

    Mar 5, 2013 at 0:28

  • 9

    @Damian yes, OUTER JOIN and FULL OUTER JOIN are equivalent, along with LEFT/RIGHT JOIN are equivalent to LEFT/RIGHT OUTER JOIN, in the same way INNER JOIN is equivalent to a simple JOIN

    – Chris

    Jan 29, 2019 at 22:26

  • 13

    I have downvoted this because it is wrong. Please consider removing the answer as it will mislead generations of computer science students who are fooled by the large upcote count. Venn diagrams do not explain join. The inner part of a join is not intersection.

    May 3, 2020 at 7:00

893

+500

The Venn diagrams don’t really do it for me.

They don’t show any distinction between a cross join and an inner join, for example, or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate.

There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway.

  1. Imagine a cross join.
  2. Evaluate the on clause against all rows from step 1 keeping those where the predicate evaluates to true
  3. (For outer joins only) add back in any outer rows that were lost in step 2.

(NB: In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same)

I’ll start off with an animated version of a full outer join. Further explanation follows.

enter image description here


Explanation

Source Tables

enter link description here

First start with a CROSS JOIN (AKA Cartesian Product). This does not have an ON clause and simply returns every combination of rows from the two tables.

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

enter link description here

Inner and Outer joins have an “ON” clause predicate.

  • Inner Join. Evaluate the condition in the “ON” clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.
  • Left Outer Join. Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns.
  • Right Outer Join. Same as inner join then for any rows in the right table that did not match anything output these with NULL values for the left table columns.
  • Full Outer Join. Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join.

Some examples

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

The above is the classic equi join.

Inner Join

Animated Version

enter image description here

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (‘Green’,’Blue’)

The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating A.Colour NOT IN ('Green','Blue') on each row of the cross join returns.

inner 2

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1

The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won’t repeat the picture of the 16 rows again.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with NULL values for the right hand columns.

LOJ

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

This simply restricts the previous result to only return the rows where B.Colour IS NULL. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B. This is known as an anti semi join.

It is important to select a column for the IS NULL test that is either not nullable or for which the join condition ensures that any NULL values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULL value for that column in addition to the un matched rows.

loj is null

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.

ROJ

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.

FOJ

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

No rows in the cross join match the 1=0 predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.

FOJ 2

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

With a minor amend to the preceding query one could simulate a UNION ALL of the two tables.

UNION ALL

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour=”Green”

Note that the WHERE clause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join…

LOJ

… And then the “Where” clause runs. NULL= 'Green' does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.

LOJtoInner

If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour=”Green”

enter image description here

SQL Fiddle

See these examples run live at SQLFiddle.com.

16

  • 64

    I will say that while this doesn’t work for me nearly as well as the Venn diagrams, I appreciate that people vary and learn differently and this is a very well presented explanation unlike any I’ve seen before, so I support @ypercube in awarding the bonus points. Also good work explaining the difference of putting additional conditions in the JOIN clause vs the WHERE clause. Kudos to you, Martin Smith.

    – Old Pro

    Dec 20, 2014 at 4:48

  • 29

    @OldPro The Venn diagrams are OK as far as they go I suppose but they are silent on how to represent a cross join, or to differentiate one kind of join predicate such as equi join from another. The mental model of evaluating the join predicate on each row of the cross join result then adding back in unmatched rows if an outer join and finally evaluating the where works better for me.

    Dec 20, 2014 at 11:05


  • 20

    The Venn diagrams are good for representing Unions and Intersections and Differences but not joins. They have some minor educational value for very simple joins, i.e. joins where the joining condition is on unique columns.

    Dec 20, 2014 at 22:28

  • 17

    @Arth – Nope you’re wrong. SQL Fiddle sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/5155 this is something the Venn diagrams can’t illustrate.

    Jan 28, 2016 at 15:54


  • 6

    How did you do these animations? Great answer, the only bit I dislike is your modesty in saying that the Venn diagrams don’t do it for you. The reality is that they are insufficient to model what’s going on and this is important to tell, lest people get the wrong idea.

    – Git Gud

    May 23, 2018 at 17:55

242

Joins are used to combine the data from two tables, with the result being a new, temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join. The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate.
For eg- Lets consider Employee and Location table:

Employee

EmpIDEmpName
13Jason
8Alex
3Ram
17Babu
25Johnson

Location

EmpIDEmpLoc
13San Jose
8Los Angeles
3Pune, India
17Chennai, India
39Bangalore, India

Inner Join:-
Inner join creates a new result table by combining column values of two tables (Employee and Location) based upon the join-predicate. The query compares each row of Employee with each row of Location to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employee and Location are combined into a result row.
Here’s what the SQL for an inner join will look like:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Now, here is what the result of running that SQL would look like:

Employee.EmpIdEmployee.EmpNameLocation.EmpIdLocation.EmpLoc
13Jason13San Jose
8Alex8Los Angeles
3Ram3Pune, India
17Babu17Chennai, India

Outer Join:-
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins and right outer joins, depending on which table’s rows are retained (left or right).

Left Outer Join:-
The result of a left outer join (or simply left join) for tables Employee and Location always contains all records of the “left” table (Employee), even if the join-condition does not find any matching record in the “right” table (Location).
Here is what the SQL for a left outer join would look like, using the tables above:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Now, here is what the result of running this SQL would look like:

Employee.EmpIdEmployee.EmpNameLocation.EmpIdLocation.EmpLoc
13Jason13San Jose
8Alex8Los Angeles
3Ram3Pune, India
17Babu17Chennai, India
25JohnsonNULLNULL

Note how while Johnson has no entry in the employee location table, he is still included in the results but the location fields are nulled.

Right Outer Join:-
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the “right” table (Location) will appear in the joined table at least once. If no matching row from the “left” table (Employee) exists, NULL will appear in columns from Employee for those records that have no match in Location.
This is what the SQL looks like:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Using the tables above, we can show what the result set of a right outer join would look like:

Employee.EmpIdEmployee.EmpNameLocation.EmpIdLocation.EmpLoc
13Jason13San Jose
8Alex8Los Angeles
3Ram3Pune, India
17Babu17Chennai, India
NULLNULL39Bangalore, India

Note how while there are no employees listed as working in Bangalore, it is still included in the results with the employee fields nulled out.

Full Outer Joins:-
Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.

Employee.EmpIdEmployee.EmpNameLocation.EmpIdLocation.EmpLoc
13Jason13San Jose
8Alex8Los Angeles
3Ram3Pune, India
17Babu17Chennai, India
25JohnsonNULLNULL
NULLNULL39Bangalore, India

MySQL 8.0 Reference Manual – Join Syntax

Oracle Join operations

3

  • 8

    best answer so far, alternative syntax – that’s what I’ve been looking for, thanks!

    – Joey

    Sep 8, 2019 at 8:21

  • 1

    The Venn diagrams are mislabelled. See my comments on the question & other answers. Also most of this language is poor. Eg: “When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employee and Location are combined into a result row.” No, not “When the join-predicate is satisfied by matching non-NULL values”. Values in rows don’t matter other than whether the condition as a whole being true or false. Some values could well be NULL for a true condition.

    – philipxy

    Nov 6, 2019 at 7:00


  • @Persistence What is needed for the tables is text that is table initialization code formatted in columns that is suitable to copy & paste & run.

    – philipxy

    Nov 26, 2021 at 12:24