CODEWITHSUNDEEP
sqlinner-joinouter-join
freenight
freenight

Reputation: 1089

Compare inner join and outer join SQL statements

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

Upvotes: 8

Views: 6247

Answers (7)

Randy Minder
Randy Minder

Reputation: 48402

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

Upvotes: -1

marc_s
marc_s

Reputation: 754438

Check out Jeff Atwood's excellent:

A Visual Explanation of SQL Joins

Marc

Upvotes: 23

DKSRathore
DKSRathore

Reputation: 3063

Using mathematical Set,

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

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

Upvotes: 0

Kris C
Kris C

Reputation: 2848

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

Upvotes: 0

Mick Walker
Mick Walker

Reputation: 3847

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.

Upvotes: 2

Glen
Glen

Reputation: 22290

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

Upvotes: 4

Galwegian
Galwegian

Reputation: 42227

INNER JOIN returns rows that exist in both tables

OUTER JOIN returns all rows that exist in either table

Upvotes: 0

Related Questions