Compare inner join and outer join SQL statements

What is the difference between an inner join and outer join? What'开发者_如何学Gos the precise meaning of these two kinds of joins?

Check out Jeff Atwood's excellent:

A Visual Explanation of SQL Joins

Marc

Wikipedia has a nice long article on the topic [here](http://en.wikipedia.org/wiki/Join_(SQL))

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

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.

INNER JOIN returns rows that exist in both tables

OUTER JOIN returns all rows that exist in either table

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

Using mathematical Set,

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

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

Assume an example schema with customers and order:

• INNER JOIN: Retrieves customers with orders only.

• LEFT OUTER JOIN: Retrieves all customers with or without orders.

• RIGHT OUTER JOIN: Retrieves all orders with or without matching customer records.

For a slightly more detailed infos, see Inner and Outer Join SQL Statements