• Inner Join Vs Outer Join Vs Union
• Sql Inner Join Vs Outer Join
• Inner Join Vs Outer Join Tableau

What Is Inner and Left Outer Join. By: Pavanpraveen First know about what is inner join The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view. You use INNER JOIN to return all rows from both tables where there is a match. In the resulting table all the rows and colums will have values. In OUTER JOIN the relulting table may have empty colums.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. Inner joins eliminate the rows that do not match with a row from the other table. Outer Joins: Outer joins can be a left, a right, or full outer join Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

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

Martin SmithChris de VriesChris de Vries

24 Answers

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:

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.

Left outer join

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

Right outer join

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

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.

Darryl HeinMark HarrisonMark Harrison

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.

Source Tables

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

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.

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

The above is the classic equi join.

Animated Version

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.

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.

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.

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.

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.

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.

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.

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

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

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'

SQL Fiddle

See these examples run live at SQLFiddle.com.

Martin SmithMartin Smith

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:

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:

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

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:

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

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:

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

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.

ajitksharmaajitksharma

Inner Join

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

Left Outer Join

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

Full Outer Join

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

References

Peter MortensenTushar Gupta - curioustusharTushar Gupta - curioustushar

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

Peter Mortensenvidyadharvidyadhar

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.

1800 INFORMATION1800 INFORMATION

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

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

If you change it to OUTER LEFT JOIN

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.

Brian BoatrightBrian Boatright

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

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 :

Lets assume now for 2 tables

1.employees , 2.phone_numbers_employees

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 :

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.

Outer joins :

Diagramatically it looks like :

PratikPratik

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.

shA.t

This is a good diagrammatic explanation for all kind of joins

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

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.

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:

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

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:

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)

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

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

shA.tshA.t

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

Sandesh

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:

Lajos VeresLajos Veres

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:

• .. ON T1.title = T2.title AND T1.version < T2.version ( => see this post as a sample usage: Select only rows with max value on a column)
• .. ON T1.y IS NULL
• .. ON 1 = 0 (just as sample)

Inner Join Vs Outer Join Vs Union

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

S.SerpooshanS.Serpooshan

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:

onedaywhenonedaywhen

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

PremrajPremraj

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

Sql Inner Join Vs Outer Join

Matched + Left Unmatched + Right Unmatched = Full Outer Join

Matched + Left Unmatched = Left Outer Join

Inner Join Vs Outer Join Tableau

Matched + Right Unmatched = Right Outer Join

Matched = Inner Join

Akshay KhaleAkshay Khale

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.

The world feels much larger than the first game.

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.

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:

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

Example:

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.

Advantages of Joins

1. Executes faster.

• 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

SandeshSandesh

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.

philipxyphilipxy

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.

Consider below 2 tables:

EMP

Department

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:

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.

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.

Mayank PorwalMayank Porwal

Not the answer you're looking for? Browse other questions tagged sqldatabasejoininner-joinouter-join or ask your own question.