Sql – Outer JOIN vs Inner JOIN

sqlsql-server-2008

I have always thought that Inner Join performs better than Outer JOIN, but I've experienced several cases now where this simply isn't true…

The other day I was creating a query with a series of INNER JOINS. Inner join was appropriate for all these joins as I only wanted rows matched in all joined tables.

I noticed that performance was very bad (1 min to run the query). The weird thing was that when I switched a few of them to Outer JOINs the query finished in a few seconds…
It's not a case of warm up or caching because I restarted the SQL server in between runs, and the performance readings were consistent over time.

I experienced this same situation on two different reports, but the common theme was that performance was improved by switching to LEFT JOIN. I made the switch for the same tables for both reports. All JOINS were done ON GUID columns

Left JOIN gave the same number of rows since all ids were matched in the JOINED tables, so it was ok to switch, but I am curious if anyone has an explanation.
Any advice on this?

The platform was SQL Server 2008 and all outer JOINS were LEFT JOINS

Best Answer

The inner join is faster than left join.

This answer should give you some tips.

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

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.

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

Sql – Difference between JOIN and INNER JOIN

They are functionally equivalent, but INNER JOIN can be a bit clearer to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

Related Topic