Compare inner join and outer join SQL statements

Question

What is the difference between an inner join and outer join? What's the precise meaning of these two kinds of joins?

Answer 1

INNER JOIN returns rows that exist in both tables

OUTER JOIN returns all rows that exist in either table

Answer 2

Wikipedia has a nice long article on the topic here

But basically :

• Inner joins return results where there are rows that satisfy the where clause in ALL tables
• Outer joins return results where there are rows that satisfy the where clause in at least one of the tables

Answer 3

Check out Jeff Atwood's excellent:

A Visual Explanation of SQL Joins

INNER JOIN

LEFT OUTER JOIN

Marc

Answer 4

Wikipedia to the rescue:

Inner Joins

Outer Joins

Answer 5

You use INNER JOIN to return all rows from both tables where there is a match. ie. 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.

Answer 6

Google to the rescue!

http://database.ittoolbox.com/documents/inner-and-outer-join-sql-statements-18442

Answer 7

Inner join only returns a joined row if the record appears in both table. Outer join depending on direction will show all records from one table, joined to the data from them joined table where a corresponding row exists

Answer 8

See the following explanations from MSDN:

Using Inner Joins

Using Outer Joins

Answer 9

Using mathematical Set,

Inner Join is A ^ B;
Outer Join is A - B.

So it is (+) is your A side in the query.