SQL: difference between inner and outer join

Question

What is the difference between INNER JOIN and OUTER JOIN?

Answer 1

A (left) 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.

Answer 2

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 a Orders and a OrderDetails table. They are related by an "OrderID"

Orders

• OrderID
• CustomerName

OrderDetails

• OrderDetailID
• OrderID
• ProductName
• Qty
• Price

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

Answer 3

@Kevin: An outer join is not necessarily faster or slower. An inner join can often be faster for the following reasons:

1. It can allow the use of a more restrictive index
2. It can reduce the number of rows passed to a sort, group or aggregate expression

@Kevin: If you are going to make a specific statement, then don't make it sound like it is generally applicable otherwise you'll get called on it. If you've ever actually used any DBMS at all in a real life situation, you would know that in some circumstances an inner join can be faster and in some circumstances an outer join can be faster.

You'll note that I did not say "an inner join is always faster", nor did I say "an outer join is always faster".

One example where an inner join is faster is where the results have to be sorted. e,g:

Table 1 has 10000000 rows Table 2 has 1 row

This will return 1 row almost instantly:

SELECT Name, ID from T1 left inner join T2 on T1.id=T2.id sort by name

This will return 10000000 rows when you come back from lunch:

SELECT Name, ID from T1 left outer join T2 on T1.id=T2.id sort by name

Answer 4

Assuming you're joining on columns with no duplicates, which is by far the most 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

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

Answer 5

This is a link to a great answer

Great answer - inner versus outer joins

Answer 6

Inner join requires there is atleast match in comparing two tables. For example table A and table B. which implies A ٨ B(A intersection B)

Left outer join and left join are same. It gives all the records match in both tables and all posibilities of left table.

similarly, Right outer join and right join are same. It gives all the records match in both tables and all posibilities of right table.

Full join is the combination of left outer join and right outer join without duplication.