JOIN types.

[grande]

This isn't really a necessity question, just a curiosity really.

What's the difference between LEFT JOIN, RIGHT JOIN, INNER JOIN, (OUTER JOIN?).

Thanks!

-------------------------
Just call me Captain Awesome.

 

[r937]

five seconds to paste them words into google and get these results --

http://www.microsoft-accesssolutions.co.uk/query_joins.htm

http://www.experts-exchange.com/Databases/MS_Access/Q_20950529.html

http://exchange.manifold.net/manifold/manuals/5_userman/mfd50OUTER_LEFT_RIGHT_FULL_JOIN_Opera.htm

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[alvechurchdata]

Taking the classic Customer and Orders tables, all types of join return information from both tables as follows:

INNER JOIN
All customers who have orders.

LEFT JOIN
All customers whether or not they've got orders. If a customer has no orders then nulls will be displayed in their order details.

RIGHT JOIN
All orders whether or not they relate to a customer. If an order does not relate to a customer then nulls will be displayed in their customer details.

OUTER JOIN
All customers and all orders. As above, nulls will fill any gaps.

CROSS JOIN
Every customer linked to every order whether or not that order belongs to that customer. Typically generates a huge and useless output set.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[r937]

couple of very minor modifications to your summary, geoff

LEFT and RIGHT are really two types of OUTER JOIN

OUTER JOIN as you describe it is actually the FULL OUTER JOIN, and you have to say FULL -- or, at least, you would if access supported it




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[lespaul]

more reading material

Understanding SQL Joins

Leslie

 

[grande]

So... Is there any practical use for a FULL OUTER join?

-------------------------
Just call me Captain Awesome.

 

[r937]

practical use for full outer join?

yes, when you want rows from both the left and the right tables, including those that match as well as those from either side that don't

for example,

customers with the product they ordered = inner
+
customers who ordered no products = left outer, unmatched
+
products not ordered by any customers = right outer, unmatched
=
full outer join


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

Is there any practical use for a FULL OUTER join
Access (in fact JetSQL) doesn't supoorts FULL OUTER JOIN).
The workaround is an UNION of a LEFT JOIN and an unmatched RIGHT JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244