By Chris de Vries


2008-09-01 22:36:06 8 Comments

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

28 comments

@matthew david 2019-04-24 20:59:06

SQL JOINs Explained

The difference is whether you want a row in the table you are creating

  • Every-time there is a match between both tables on the related column - INNER JOIN
  • Every-time there is a row in the first table (bring in matches on the related column if they exist) - LEFT JOIN
  • Every-time there is a row in the second table (bring in matches on the related column if they exist) - RIGHT JOIN
  • Every-time there is a match or not a match on the related column in both tables - FULL OUTER JOIN

This article breaks down the graphic above into more detail: https://dataschool.com/learn/sql-join-types-explained-visualizing-sql-joins-and-building-on-the-classic-venn-diagrams

I also created animations to better show how the data is being manipulated in each type.

INNER JOIN: https://dataschool.com/learn/common-sql-join-types-inner-join

LEFT JOIN & RIGHT JOIN: https://dataschool.com/learn/common-sql-join-types-left-right-join

FULL OUTER JOIN: https://dataschool.com/learn/common-sql-join-types-full-outer-join

@Premraj 2017-12-26 16:49:13

enter image description here

  • INNER JOIN most typical join for two or more tables. It returns data match on both table ON primarykey and forignkey relation.
  • OUTER JOIN is same as INNER JOIN, but it also include NULL data on ResultSet.
    • LEFT JOIN = INNER JOIN + Unmatched data of left table with Null match on right table.
    • RIGHT JOIN = INNER JOIN + Unmatched data of right table with Null match on left table.
    • FULL JOIN = INNER JOIN + Unmatched data on both right and left tables with Null matches.
  • Self join is not a keyword in SQL, when a table references data in itself knows as self join. Using INNER JOIN and OUTER JOIN we can write self join queries.

For example:

SELECT * 
FROM   tablea a 
       INNER JOIN tableb b 
               ON a.primary_key = b.foreign_key 
       INNER JOIN tablec c 
               ON b.primary_key = c.foreign_key 

@ArifMustafa 2018-04-19 13:25:30

good and simpler to understand.

@Mayank Porwal 2018-11-25 14:50:41

Consider below 2 tables:

EMP

empid   name    dept_id salary
1       Rob     1       100
2       Mark    1       300
3       John    2       100
4       Mary    2       300
5       Bill    3       700
6       Jose    6       400

Department

deptid  name
1       IT
2       Accounts
3       Security
4       HR
5       R&D

Inner Join:

Mostly written as just JOIN in sql queries. It returns only the matching records between the tables.

Find out all employees and their department names:

Select a.empid, a.name, b.name as dept_name
FROM emp a
JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security

As you see above, Jose is not printed from EMP in the output as it's dept_id 6 does not find a match in the Department table. Similarly, HR and R&D rows are not printed from Department table as they didn't find a match in the Emp table.

So, INNER JOIN or just JOIN, returns only matching rows.

LEFT JOIN :

This returns all records from the LEFT table and only matching records from the RIGHT table.

Select a.empid, a.name, b.name as dept_name
FROM emp a
LEFT JOIN department b
ON a.dept_id = b.deptid
;

empid   name    dept_name
1       Rob     IT
2       Mark    IT
3       John    Accounts
4       Mary    Accounts
5       Bill    Security
6       Jose    

So, if you observe the above output, all records from the LEFT table(Emp) are printed with just matching records from RIGHT table.

HR and R&D rows are not printed from Department table as they didn't find a match in the Emp table on dept_id.

So, LEFT JOIN returns ALL rows from Left table and only matching rows from RIGHT table.

Can also check DEMO here.

@philipxy 2018-11-27 01:19:10

Nothing in this clearly describes what either join does. (Nor does it address a "difference" between them, other than saying they are different.) This adds nothing to the many answers (many highly upovoted) of a10-yr old question--is it "helpful"?

@S.Serpooshan 2016-11-08 12:25:02

The precise algorithm for INNER JOIN, LEFT/RIGHT OUTER JOIN are as following:

  1. Take each row from the first table: a
  2. Consider all rows from second table beside it: (a, b[i])
  3. Evaluate the ON ... clause against each pair: ON( a, b[i] ) = true/false?
    • When the condition evaluates to true, return that combined row (a, b[i]).
    • When reach end of second table without any match, and this is an Outer Join then return a (virtual) pair using Null for all columns of other table: (a, Null) for LEFT outer join or (Null, b) for RIGHT outer join. This is to ensure all rows of first table exists in final results.

Note: the condition specified in ON clause could be anything, it is not required to use Primary Keys (and you don't need to always refer to Columns from both tables)! For example:

Inner Join vs. Left Outer Join


enter image description here

Note: Left Join = Left Outer Join, Right Join = Right Outer Join.

@Brian Boatright 2008-09-01 22:47:39

Inner joins require that a record with a related ID exist in the joined table.

Outer joins will return records for the left side even if nothing exists for the right side.

For instance, you have an Orders and an OrderDetails table. They are related by an "OrderID".

Orders

  • OrderID
  • CustomerName

OrderDetails

  • OrderDetailID
  • OrderID
  • ProductName
  • Qty
  • Price

The request

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
 INNER JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

will only return Orders that also have something in the OrderDetails table.

If you change it to OUTER LEFT JOIN

SELECT Orders.OrderID, Orders.CustomerName
  FROM Orders 
  LEFT JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID

then it will return records from the Orders table even if they have no OrderDetails records.

You can use this to find Orders that do not have any OrderDetails indicating a possible orphaned order by adding a where clause like WHERE OrderDetails.OrderID IS NULL.

@PhiLho 2013-01-05 11:11:24

I appreciate the simple yet realistic example. I changed a request like SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c, categories_description cd WHERE c.id = cd.categories_id AND c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC to SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c INNER JOIN categories_description cd ON c.id = cd.categories_id WHERE c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC (MySQL) with success. I wasn't sure about the additional conditions, they mix well...

@ajitksharma 2014-12-18 06:54:55

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. Lets consider Employee and Location table:

enter image description here

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: enter image description here enter image description here

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: enter image description here enter image description here

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:

enter image description hereenter image description here

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. enter image description here

Image Source

MySQL 8.0 Reference Manual - Join Syntax

Oracle Join operations

@Martin Smith 2014-12-13 11:58:59

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.

@Old Pro 2014-12-20 04:48:36

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.

@Martin Smith 2014-12-20 11:05:32

@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.

@ypercubeᵀᴹ 2014-12-20 22:28:49

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.

@luk2302 2015-05-04 08:08:47

is there a way to get the two green rows and the one blue row of A without using distinct?

@Martin Smith 2015-05-04 11:07:29

@luk2302 - Yes, you need a semi join. There is no semi join keyword in SQL though. Instead you can use in or exists. i.e. SELECT A.Colour FROM A WHERE A.Colour IN (SELECT B.Colour FROM B) or alternatively SELECT A.Colour FROM A WHERE EXISTS (SELECT * FROM B WHERE A.Colour = B.Colour)

@luk2302 2015-05-04 11:09:17

thanks, i already ended up using where exists and it works. But I hoped there was a way to achieve that with just a regular join. Thanks anyway! And btw: far better representation of joins than by venn diagrams :)

@Arth 2016-01-28 15:47:01

Pretty sure a lot of your JOIN results have too many green rows.. that first INNER JOIN should only have 2 green rows for example. In fact pretty much all the cases where you have 4 green rows consecutively should all only be 2 rows.

@Martin Smith 2016-01-28 15:54:10

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

@Arth 2016-01-28 15:57:18

@MartinSmith Wow, I agree, I'm totally wrong! Too used to working with one-to-manys.. thanks for the correction.

@philipxy 2017-07-28 08:45:55

Unfortunately, your illustrations don't illustrate of LEFT vs INNER JOIN ON in the general case. And although most of the Venn diagrams here are also limited to illustrating special cases, a Venn diagram, when properly interpreted, can represent inner vs outer join, with the intersection holding your green-green INNER JOIN rows. See my other comments.

@Basil Bourque 2017-09-16 19:35:24

I re-created these excellent examples on SQLFiddle.com for anyone to try running. Uncomment any of the queries you wish to run.

@Git Gud 2018-05-23 17:55:03

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.

@zarose 2018-12-07 19:12:14

@GitGud I'd contest that the Venn diagrams, while incomplete, supplement this answer for me. Like what was said earlier, people have different learning styles.

@philipxy 2019-03-19 00:02:34

@zarose The answers using Venn diagrams are unclear, incomplete & wrong, including in their use of Venn diagrams. See my first comment on the question & elsewhere on this page. Tolerating such writing is not a learning style. It's clearly a writing style. And (from the upvotes & comments) a reading style.

@philipxy 2017-09-07 08:35:16

left join on (aka left outer join on) returns inner join on rows union all unmatched left table rows extended by nulls.

right join (on aka right outer join on) returns inner join on rows union all unmatched right table rows extended by nulls.

full join on (aka full outer join on) returns inner join on rowsunion all unmatched left table rows extended by nulls union all unmatched right table rows extended by nulls.

(SQL Standard 2006 SQL/Foundation 7.7 Syntax Rules 1, General Rules 1 b, 3 c & d, 5 b.)

So don't outer join until you know what underlying inner join is involved.


Find out what rows inner join returns.

Read my comments there re the many confused & poor answers.

Then read my comments here re the many confused & poor answers.

@onedaywhen 2019-04-24 16:08:07

I have indeed read your many comments. When you say, "a Venn diagram, when properly interpreted, can represent inner vs outer join" do you mean when properly interpreted by the observer or the Venn diagram itself? If the latter, please draw it :)

@philipxy 2019-04-24 16:31:27

I'm not sure what you are trying to say. I am talking about the standard interpretation of a Venn diagram as sets of elements. (Because some uses of diagrams don't even manage that.) "Properly" for an application includes saying what the sets and/or elements are. See comment at the top of this page with 50 upvotes re a Venn diagram for inner vs outer joins. I'll edit some of my comments into this question. I don't want a Venn diagram in this post.

@onedaywhen 2016-09-30 11:04:26

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

They are the most commonly used existential operators in SQL, where INNER JOIN is used for 'exists' and LEFT OUTER JOIN is used for 'does not exist'.

Consider these queries:

users who have posted and have votes
users who have posted but have no badges

People who look for set-based solutions (an industry term) would recognise the respective queries as:

users who have posted INTERSECT users who have votes
users who have posted MINUS users who have badges

Translating these into standard SQL:

SELECT UserId FROM Posts
INTERSECT 
SELECT UserId FROM Votes;

SELECT UserId FROM Posts
EXCEPT 
SELECT UserId FROM Badges;

Others will think along similar lines of set inclusion:

users who have posted and IN the set of users who have votes
users who have posted and NOT IN the set of users who have badges

Translating these into standard SQL:

SELECT UserId 
  FROM Posts
 WHERE UserId IN ( SELECT UserId FROM Votes );

SELECT UserId 
  FROM Posts
 WHERE UserId NOT IN ( SELECT UserId FROM Badges );

Some will think in terms of 'existance' within sets e.g.

users who have posted and EXIST in the set of users who have votes
users who have posted and do NOT EXIST in the set of users who have badges

Translating these into standard SQL (note we now need to use range variables i.e. p, v, b):

SELECT p.UserId 
  FROM Posts p
 WHERE EXISTS ( SELECT *
                  FROM Votes v
                 WHERE v.UserId = p.UserId );

SELECT p.UserId 
  FROM Posts p
 WHERE NOT EXISTS ( SELECT *
                      FROM Badges b
                     WHERE b.UserId = p.UserId );

However, I've found that the "industry standard" approach is to exclusively use joins. I don't know what the thinking is here (Law of the Instrument? Premature optimization?), so I'll go straight to the syntax:

SELECT p.UserId 
  FROM Posts p
       INNER JOIN Votes v ON v.UserId = p.UserId;

SELECT p.UserId 
  FROM Posts p
       LEFT JOIN Badges b ON b.UserId = p.UserId
 WHERE b.UserId IS NULL;

Things to note:

  • The only projection is from Users but we still need all those range variables (p, v, b) for search conditions.
  • The UserId IS NULL search condition 'belongs' to the the OUTER JOIN but is disconnected in the query.
  • LEFT is the industry standard: professionals will rewrite a query to avoid using RIGHT!
  • The OUTER keyword from LEFT OUTER JOIN is omitted.

Closing remarks:

Sometimes joins are used in queries solely to determine whether values exist or do not exists in another set. Learn to look carefully at the attributes being projected (the columns in the SELECT clause): if there are none from the joined table then they are simply being used as existential operators. Additionally for outer join, look for instances of <key_column> IS NULL in the WHERE clause.

@rashedcs 2017-10-17 12:25:56

The difference between inner join and outer join is as follow:

  1. Inner join is a join that combined tables based on matching tuples, whereas outer join is a join that combined table based on both matched and unmatched tuple.
  2. Inner join merges matched row from two table in where unmatched row are omitted, whereas outer join merges rows from two tables and unmatched rows fill with null value.
  3. Inner join is like an intersection operation, whereas outer join is like an union operation.
  4. Inner join is two types, whereas outer join are three types.
  5. Inner join is slower, whereas outer join is faster than inner join.

@Mark Harrison 2008-09-01 22:59:34

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

@softveda 2009-08-30 12:59:48

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.

@Thomas 2011-05-03 19:57:42

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.

@D3 K 2011-10-28 03:55:24

Very nice, Mark can you explain MySQL performance tuning in similar way. Lot of beginners really need some simple and solid explanation of performance areas.

@YvesR 2012-04-17 18:53:28

Pro answer! Liked it. Btw: I still can't find the difference betweek LEFT JOIN and LEFT OUTER JOIN, etc. . If this is not part of the question I can post a new one :)

@Vytautas 2012-04-25 06:42:19

what is b.b(+) or this is just mistake newer seen anything like that..

@Mark Harrison 2012-04-25 18:19:49

@Vytautas, (+) is the ansi sql syntax for joins. most databases support both syntaxes.

@Tim Lovell-Smith 2012-11-19 01:03:20

Why are there two different syntaxes in each code sample?

@Mark Harrison 2012-11-19 22:47:49

@TimLovell-Smith, the (+) form is the ansi syntax. Most databases support both syntaxes.

@Damian 2013-01-01 20:29:27

Am I right that FULL JOIN is an alias of FULL OUTER JOIN and LEFT JOIN is an alias of LEFT OUTER JOIN ?

@Ameer 2013-03-04 08:39:36

yes great and excellent explanation. but why in column b the values are not in order? i.e it is 6,5 not as 5,6?

@Mark Harrison 2013-03-05 00:28:16

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

@Koray Tugay 2013-05-01 10:39:17

Can you include natural join in your example?

@JJD 2013-08-08 23:20:34

@MarkHarrison Maybe you can help with multiple duplicates?

@codetaku 2013-08-12 14:56:53

Thomas: Actually, you and Mark are both wrong about a different part of the phrasing. "The outer parts of a venn diagram" refers to "A U B \ A ∩ B". So Mark accidentally directly contradicted himself, though the remainder of his explanation was obviously clear enough to make up for that.

@Birupakhya Dash 2014-01-15 16:35:08

To say that outer join is a union may sound a little confusing when the result set of A left outer join B doesn't have the non-matching elements of B. Isn't it?

@jdero 2014-07-02 19:30:37

"Assuming you're joining on columns with no duplicates, which is a very common case:" - Does much change when this is not the case?

@nomen 2014-08-01 16:09:15

A join doesn't give you the union or intersection of anything. It gives you a closure in a lattice.

@philipxy 2016-01-20 06:09:25

The + syntax was never ANSI, it was Oracle, and it was always inadequate for expressions using multiple tables or comparisons. The ON syntax is ANSI. PS The comparison to Venn diagrams is phrased wrong. See my comment on the question.

@philipxy 2016-06-17 22:44:05

Here is the correct statement for intersection/union of circles/sets A & B in terms of tables A & B. Circle A = A LEFT JOIN B ON condition. Circle B = (A RIGHT JOIN B ON condition). (A FULL OUTER JOIN B on condition) = Circle A UNION Circle B. (A INNER JOIN B ON *condition) = Circle A INTERSECT Circle B. Circle A MINUS Circle B = (A rows not matched by A JOIN B extended by NULLs). Circle B MINUS Circle A = (B rows not matched by A JOIN B extended by NULLs). See my comment (the first) on the question.

@philipxy 2017-07-24 12:43:05

@HelenNeely Unfortunately this explanation is wrong. Its bullets are only correct when all the columns are the same, and its language is fuzzy. See my comment immediately before yours, and my comment on the question.

@Chris 2019-01-29 22:26:21

@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

@philipxy 2019-03-18 23:48:47

@Chris INNER JOIN cannot be shortened to JOIN in standard SQL. A DBMS might allow it. MySQL does.

@shA.t 2015-04-13 13:07:50

The answer is in the meaning of each one, so in the results.

Note :
In SQLite there is no RIGHT OUTER JOIN or FULL OUTER JOIN.
And also in MySQL there is no FULL OUTER JOIN.

My answer is based on above Note.

When you have two tables like these:

--[table1]               --[table2]
id | name                id | name
---+-------              ---+-------
1  | a1                  1  | a2
2  | b1                  3  | b2

CROSS JOIN / OUTER JOIN :
You can have all of those tables data with CROSS JOIN or just with , like this:

SELECT * FROM table1, table2
--[OR]
SELECT * FROM table1 CROSS JOIN table2

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2
1  | a1   | 3  | b2
2  | b1   | 1  | a2
2  | b1   | 3  | b2

INNER JOIN :
When you want to add a filter to above results based on a relation like table1.id = table2.id you can use INNER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
--[OR]
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id | name 
---+------+----+------
1  | a1   | 1  | a2

LEFT [OUTER] JOIN :
When you want to have all rows of one of tables in the above result -with same relation- you can use LEFT JOIN:
(For RIGHT JOIN just change place of tables)

SELECT * FROM table1, table2 WHERE table1.id = table2.id 
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
--[OR]
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

--[Results:]
id | name | id   | name 
---+------+------+------
1  | a1   | 1    | a2
2  | b1   | Null | Null

FULL OUTER JOIN :
When you also want to have all rows of the other table in your results you can use FULL OUTER JOIN:

SELECT * FROM table1, table2 WHERE table1.id = table2.id
UNION ALL
SELECT *, Null, Null FROM table1 WHERE Not table1.id In (SELECT id FROM table2)
UNION ALL
SELECT Null, Null, * FROM table2 WHERE Not table2.id In (SELECT id FROM table1)
--[OR] (recommended for SQLite)
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL
--[OR]
SELECT * FROM table1 FULL OUTER JOIN table2 On table1.id = table2.id

--[Results:]
id   | name | id   | name 
-----+------+------+------
1    | a1   | 1    | a2
2    | b1   | Null | Null
Null | Null | 3    | b2

Well, as your need you choose each one that covers your need ;).

@potashin 2015-06-14 13:34:13

You can add to your note, that there is no full outer join in MySQL either.

@Laxmi 2017-07-10 11:56:47

1.Inner Join: Also called as Join. It returns the rows present in both the Left table, and right table only if there is a match. Otherwise, it returns zero records.

Example:

SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
INNER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id

output1

2.Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table, and right table.

Example:

SELECT
  e1.emp_name,
  e2.emp_salary    
FROM emp1 e1
FULL OUTER JOIN emp2 e2
  ON e1.emp_id = e2.emp_id

output2

3.Left Outer join: Or simply called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).

4.Right Outer Join: Also called as Right Join. It returns matching rows from the left table (if any), and all the rows present in the Right table.

joins

Advantages of Joins

  1. Executes faster.

@philipxy 2017-07-24 13:02:37

This is only correct when the tables have the same column set. (It confuses inner join with intersection & full join with union.) Also "match" is undefined. Read my other comments.

@Raghu K Nair 2016-03-21 22:57:39

This is a good explanation for joins

This is a good diagrammatic explanation for all kind of joins

source: http://ssiddique.info/understanding-sql-joins-in-easy-way.html

@Anands23 2016-10-13 08:53:11

In Simple Terms,

1.INNER JOIN OR EQUI JOIN : Returns the resultset that matches only the condition in both the tables.

2.OUTER JOIN : Returns the resultset of all the values from both the tables even if there is condition match or not.

3.LEFT JOIN : Returns the resultset of all the values from left table and only rows that match the condition in right table.

4.RIGHT JOIN : Returns the resultset of all the values from right table and only rows that match the condition in left table.

5.FULL JOIN : Full Join and Full outer Join are same.

@Sandesh 2016-02-02 11:43:28

  • 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.

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

  • 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 versay

@philipxy 2017-02-03 11:15:30

This is both wrong and unclear. Join is not an intersection unless the tables have the same columns. Outer joins don't have rows from A or B unless they have the same columns, in which case there are not nulls added. You are trying to say something, but you are not saying it. You are not explaining correctly or clearly.

@Isaac Lem 2017-04-11 09:23:49

@philipxy: Disagreed on your statement Join is not an intersection unless the tables have the same columns No. You can join any columns that you want and if the value match, they will join together.

@philipxy 2017-04-11 14:24:05

That comment is as unclear as your answer. (I suppose you might be thinking something like, the set of subrow values for the common columns of the result is the intersection of the sets of subrow values for the common columns of each of the inputs; but that's not what you have written. You are not clear.)

@Akshay Khale 2016-04-28 09:10:48

Simplest Definitions

Inner Join: Returns matched records from both tables.

Full Outer Join: Returns matched and unmatched records from both tables with null for unmatched records from Both Tables.

Left Outer Join: Returns matched and unmatched records only from table on Left Side.

Right Outer Join: Returns matched and unmatched records only from table on Right Side.

In-Short

Matched + Left Unmatched + Right Unmatched = Full Outer Join

Matched + Left Unmatched = Left Outer Join

Matched + Right Unmatched = Right Outer Join

Matched = Inner Join

@yeliabsalohcin 2017-09-05 09:22:46

This is brilliant and explains why join doesn't work as expected for Time Series index's. Time stamps one second apart are unmatched.

@philipxy 2017-11-22 02:52:49

@yeliabsalohcin You don't explain "as expected" here or "works" in your comment on the question. It's just some unexplained personal misconception you strangely expect others to have. If you treat words as sloppily when you are reading--misinterpreting clear writing and/or accepting unclear writing--as when you are writing here then you can expect to have misconceptions. In fact this answer like most here is unclear & wrong. "Inner Join: Returns matched records from both tables" is wrong when input column sets differ. It's trying to say a certain something, but it isn't. (See my answer.)

@Kanwar Singh 2014-04-11 09:18:07

Inner join.

A join is combining the rows from two tables. An inner join attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match. If a row from the first table in the join matches two rows in the second table, then two rows will be returned in the results. If there’s a row in the first table that doesn’t match a row in the second, it’s not returned; likewise, if there’s a row in the second table that doesn’t match a row in the first, it’s not returned.

Outer Join.

A left join attempts to find match up the rows from the first table to rows in the second table. If it can’t find a match, it will return the columns from the first table and leave the columns from the second table blank (null).

@onedaywhen 2016-01-22 15:23:20

Having criticized the much-loved red-shaded Venn diagram, I thought it only fair to post my own attempt.

Although @Martin Smith's answer is the best of this bunch by a long way, his only shows the key column from each table, whereas I think ideally non-key columns should also be shown.

The best I could do in the half hour allowed, I still don't think it adequately shows that the nulls are there due to absence of key values in TableB or that OUTER JOIN is actually a union rather than a join:

enter image description here

@LearnByReading 2016-01-22 19:32:48

Question is asking for Difference between INNER and OUTER joins though, not necessarily left outer join lol

@ya23 2009-08-30 11:52:37

I recommend Jeff's blog article. The best description I've ever seen, plus there is a visualization, e.g.:

Inner Join:

enter image description here

Full Outer Join:

enter image description here

@softveda 2009-08-30 12:56:58

This diagram is a bit misleading for the concept. Read the comments in the post as well.

@ursitesion 2014-02-12 09:20:58

@ya23: what does you mean by full outer join?

@philipxy 2015-11-13 01:22:02

Except that Jeff repudiates his blog a few pages down in the comments: "The commenters pointing out that the diagrams break down in case of multiple and or duplicate results, are absolutely right. I was actually thinking of joins along the primary key, which tends to be unique by definition, although the examples are not expressed that way. Like the cartesian or cross product, anything that results in more rows than you originally started with does absolutely breaks the whole venn diagram concept. So keep that in mind."

@Galax 2016-01-19 16:18:12

@philipxy yes the Venn diagram analogy only works for the primary key after ignoring NULLs- this is often not stated. The Venn diagrams don't help to explain how the other columns in the resulting set are filled with NULLs.

@philipxy 2016-01-20 06:27:36

@Galax Unfortunately, whether the joins are on keys (let alone primary keys) doesn't help the diagrams to make sense. (Even though I quoted Jeff saying that he thinks it does.) (I don't understand your "works for the primary key after ignoring NULLs" or "the other columns in the resulting set are filled with NULLs". I know more or less the sort of thing you are trying to say but any correspondence but the one in my comment to the question is complex and so is a clear explanation. Although I would welcome one. Exactly what are being grouped how by the circles?

@vijikumar 2012-09-27 07:33:12

You use INNER JOIN to return all rows from both tables where there is a match. i.e. In the resulting table all the rows and columns will have values.

In OUTER JOIN the resulting table may have empty columns. Outer join may be either LEFT or RIGHT.

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

@Lajos Veres 2013-11-17 12:59:46

I don't see much details about performance and optimizer in the other answers.

Sometimes it is good to know that only INNER JOIN is associative which means the optimizer has the most option to play with it. It can reorder the join order to make it faster keeping the same result. The optimizer can use the most join modes.

Generally it is a good practice to try to use INNER JOIN instead of the different kind of joins. (Of course if it is possible considering the expected result set.)

There are a couple of good examples and explanation here about this strange associative behavior:

@Ben 2014-12-26 08:51:00

It can't possibly be "good practice" to use one type of join over another. Which join you use determines the data that you want. If you use a different one you're incorrect. Plus, in Oracle at least this answer is completely wrong. It sounds completely wrong for everything and you have no proof. Do you have proof?

@Lajos Veres 2014-12-26 11:01:45

1. I mean try to use. I saw lots of people using LEFT OUTER joins everywhere without any good reason. (The joined columns were 'not null'.) In those cases it would be definitely better to use INNER joins. 2. I have added a link explaining the non-associative behaviour better than I could.

@shA.t 2015-05-13 03:01:51

As I know INNER JOIN is slower than LEFT JOIN in most of the times, And people can use LEFT JOIN instead of INNER JOIN by adding a WHERE for removing unexpected NULL results ;).

@Lajos Veres 2015-05-13 08:04:42

These comments made me a bit uncertain. Why do you think INNER is slower?

@Pratik C Joshi 2015-02-19 04:50:21

In simple words :

Inner join -> Take ONLY common records from parent and child tables WHERE primary key of Parent table matches Foreign key in Child table.

Left join ->

pseudo code

1.Take All records from left Table
2.for(each record in right table,) {
    if(Records from left & right table matching on primary & foreign key){
       use their values as it is as result of join at the right side for 2nd table.
    } else {
       put value NULL values in that particular record as result of join at the right side for 2nd table.
    }
  }

Right join : Exactly opposite of left join . Put name of table in LEFT JOIN at right side in Right join , you get same output as LEFT JOIN.

Outer join : Show all records in Both tables No matter what. If records in Left table are not matching to right table based on Primary , Forieign key , use NULL value as result of join .

Example :

Example

Lets assume now for 2 tables

1.employees , 2.phone_numbers_employees

employees : id , name 

phone_numbers_employees : id , phone_num , emp_id   

Here , employees table is Master table , phone_numbers_employees is child table(it contains emp_id as foreign key which connects employee.id so its child table.)

Inner joins

Take the records of 2 tables ONLY IF Primary key of employees table(its id) matches Foreign key of Child table phone_numbers_employees(emp_id).

So query would be :

SELECT e.id , e.name , p.phone_num FROM employees AS e INNER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Here take only matching rows on primary key = foreign key as explained above.Here non matching rows on primary key = foreign key are skipped as result of join.

Left joins :

Left join retains all rows of the left table, regardless of whether there is a row that matches on the right table.

SELECT e.id , e.name , p.phone_num FROM employees AS e LEFT JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Outer joins :

SELECT e.id , e.name , p.phone_num FROM employees AS e OUTER JOIN phone_numbers_employees AS p ON e.id = p.emp_id;

Diagramatically it looks like :

Diagram

@philipxy 2015-08-10 04:27:54

The result has nothing to (do per se) with primary/unique/candidate keys & foreign keys. The baviour can and should be described without reference to them. A cross join is calculated, then rows not matching the ON condition are filtered out; additionally for outer joins rows filtered/unmatched rows are extended by NULLs (per LEFT/RIGHT/FULL and included.

@1800 INFORMATION 2008-09-01 22:38:43

A inner join only shows rows if there is a matching record on the other (right) side of the join.

A (left) outer join shows rows for each record on the left hand side, even if there are no matching rows on the other (right) side of the join. If there is no matching row, the columns for the other (right) side would show NULLs.

@Teoman shipahi 2013-05-16 23:03:34

Also you can consider the following schema for different join types;

visual explanation of joins

Source: Visual-Representation-of-SQL-Joins explained in detail by C.L. Moffatt

@Michael Ozeryansky 2014-03-25 02:25:52

Note: There's no FULL OUTER JOIN in MySQL. stackoverflow.com/questions/12473210/…

@AlikElzin-kilaka 2014-12-30 09:21:29

I think this diagram assumes that there are no duplicate Key, meaning Key is unique. If Key wasn't unique, I think the result would have been a cross and the return count would be much higher than the size of A.

@Kelly S. French 2016-06-28 19:27:36

In the FULL OUTER JOIN example with the intersection portion excluded, won't using 'A.Key IS NULL' exclude all rows from A because if the Key is NULL then by definition the row doesn't exist? I'm not seeing how that mechanism achieves the results for the VENN diagram shown.

@philipxy 2016-08-20 15:04:07

@KellyS.French See my comments on the question & other answers explaining how to read the Venn diagrams. The zones do not enclose rows from tables A & B. They enclose certain rows made from pairing a row from A and a row from B. Which also aren't even rows of the output of the SELECT statement. The Venn diagram answers are all poor.

@Stijn de Witt 2017-07-28 18:54:27

A represents all potential rows coming from tableA, B represents all potential rows coming from tableB. The red zones in the diagram mark which of these rows will actually be included in the result set. Note that there could be (and often are) many, many more potential rows coming from a table than there are actually rows in that table. That's because any row in tableA will be potentially occuring once for each row in tableB that references it. This is called the cartesian product.

@philipxy 2017-09-14 02:07:32

@StijndeWitt That is not clear. Italicizing "potential" doesn't define it. When the inputs don't have the same columns, no row coming form A or B is included in the result set, so your second sentence is wrong. Moreover, the answer explains nothing about the diagram, and neither does its source, which also says nonsense about rows returned.

@Stijn de Witt 2017-09-14 07:44:46

@philipxy Please do understand it's not my answer. My comment was intended to help people to understand it. If you can do better, please do so.

@philipxy 2019-03-18 23:51:42

@StijndeWitt I know it's not your answer. There is no benefit in adding fuzzy, incomplete & wrong stuff to fuzzy, incomplete & wrong stuff.

@Tushar Gupta - curioustushar 2014-01-27 12:16:05

Inner Join

Retrieve the matched rows only, that is, A intersect B.

Enter image description here

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Left Outer Join

Select all records from the first table, and any records in the second table that match the joined keys.

Enter image description here

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

Full Outer Join

Select all records from the second table, and any records in the first table that match the joined keys.

Enter image description here

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

References

@Grijesh Chauhan 2014-01-27 12:23:03

What is the name of tool? I find it is interesting as it shows number of rows and venn-diagrams

@Tushar Gupta - curioustushar 2014-01-27 12:25:31

@GrijeshChauhan Datamartist :)

@Grijesh Chauhan 2014-01-27 12:27:42

@Trushar :( it is not for Linux system..

@Tushar Gupta - curioustushar 2014-01-27 12:30:38

@GrijeshChauhan Yeah But you can Try to run it using wine .

@Grijesh Chauhan 2014-01-27 12:32:59

Ohh! yes I ..I used SQLyog using wine.. there is also PlayOnLinux

@philipxy 2018-07-24 01:09:33

Your text is unclear & wrong. The "matched rows only" are rows from the cross join of A & B & what is retrieved (A inner join B) is not A intersect B but (A left join B) intersect (A right join B). The "selected" rows are not from A & B, they are from A cross join B & from null-extended values of rows from A & B.

@vidyadhar 2013-01-12 11:07:41

In simple words:

An inner join retrieve the matched rows only.

Whereas an outer join retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

  • Left: Matched rows in the right table and all rows in the left table

  • Right: Matched rows in the left table and all rows in the right table or

  • Full: All rows in all tables. It doesn't matter if there is a match or not

@philipxy 2015-11-29 01:17:46

@nomen Not that this answer addresses it, but INNER JOIN is an intersection and FULL OUTER JOIN is the corresponding UNION if the left & right sets/circles contain the rows of (respectively) LEFT & RIGHT join. PS This answer is unclear about rows in input vs output. It confuses "in the left/right table" with "has a left/right part in the left/right" and it uses "matched row" vs "all" to mean row extended by row from other table vs by nulls.

@naga 2010-09-02 09:49:28

INNER JOIN requires there is at least a match in comparing the two tables. For example, table A and table B which implies A ٨ B (A intersection B).

LEFT OUTER JOIN and LEFT JOIN are the same. It gives all the records matching in both tables and all possibilities of the left table.

Similarly, RIGHT OUTER JOIN and RIGHT JOIN are the same. It gives all the records matching in both tables and all possibilities of the right table.

FULL JOIN is the combination of LEFT OUTER JOIN and RIGHT OUTER JOIN without duplication.

Related Questions

Sponsored Content

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

3 Answered Questions

8 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

19 Answered Questions

[SOLVED] LEFT OUTER JOIN in LINQ

  • 2010-08-04 11:18:21
  • Toy
  • 567735 View
  • 450 Score
  • 19 Answer
  • Tags:   c# linq join

14 Answered Questions

[SOLVED] How to do a FULL OUTER JOIN in MySQL?

24 Answered Questions

[SOLVED] What is the difference between UNION and UNION ALL?

  • 2008-09-08 15:19:33
  • Brian G
  • 1191548 View
  • 1260 Score
  • 24 Answer
  • Tags:   sql union union-all

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

10 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

10 Answered Questions

[SOLVED] What is the difference between Left, Right, Outer and Inner Joins?

  • 2009-01-15 19:07:31
  • MrM
  • 354324 View
  • 528 Score
  • 10 Answer
  • Tags:   sql database join

Sponsored Content