Is an outer join only used for analysis by the developer? I'm having trouble finding a use case for why you would want to include data in two or more tables that is unrelated or does not "match" your select criteria.
+1
A:
The "doesn't match" case is useful for optional data. Something which is present for some rows, but not for others.
John Saunders
2009-07-09 01:10:36
Why the downvote? Is this not accurate?
John Saunders
2009-07-09 06:46:40
+7
A:
An example use case would be to produce a report that shows ALL customers and their purchases. That is, show even customers who have not purchased anything. If you do an ordinary join of customers and purchases, the report would show only those customers with at least one purchase.
Kevin Beck
2009-07-09 01:15:25
Would it be accurate to rephrase this as, if you want to get ALL rows in columns that exist in two tables, then the only way is to use an outer join?
hal10001
2009-07-09 01:37:05
The use case I described is typically done as a left outer join, you get all rows from the left table (customers) and matching rows from the right (purchases). To guarantee that all rows from BOTH tables are represented in the result, you would use a full outer join.
Kevin Beck
2009-07-09 04:27:50
+1
A:
Perhaps you have a users table and an address table related to it, since your users may choose not to enter an address, or they may have several (shipping, street, postal, etc). If you wanted to list all your users, but also display addresses for those that have them, you'd have to use an outer join for the address table.
nizmow
2009-07-09 01:16:30
A:
Say you had two tables one for users and one for something like qualifications, you want to do a query that gets all users with their qualifications if they have any. In this case you would use an outer join to get all the users back and those with qualifications. An inner join would only give you the users who had a qualification.
Steve Temple
2009-07-09 01:17:33
A:
One example:
select CustomerName, count(OrderId) as NumOrders
from Customer C
left outer join Order O on O.CustomerId = C.CustomerId
group by CustomerName
Includes customer with no orders. Inner join would drop those customers from the result.
Shannon Severance
2009-07-09 01:29:35
+1
A:
The difference between outer and inner joins is primarily that the outer joins retain each record from both the joined tables, whether the join predicate is matched or not. So a use case would need to revolve around knowing about "missing stuff", eg. customers who haven't made purchases, or members who have missing personal details.
dave
2009-07-09 01:31:14
+1
A:
A great article about outer joins: Meet the experts: Terry Purcell on coding predicates in outer joins: A comparison of simple outer join constructs
AlexKuznetsov
2009-07-09 01:41:43
+3
A:
Here is a very good list of examples by none other than Jeff Atwood - A Visual Explanation of SQL Joins he shows a new representation visually of the SQL joins.
Shadi Almosri
2009-07-09 01:45:01
+1 @Shadi Almosri a picture is worth thousand words. Specially if you didn't pay attention to sets and Venn diagrams in schoole :)
TheVillageIdiot
2009-07-09 02:37:49
+1
A:
The OUTER JOIN is intended to address the cases where you wish to select a set of records from a primary table, which may or may not have related records contained in a secondary table.
An INNER join would omit from the list any primary records not also represented in the secondary table. Any OUTER JOIN guarantees the visiblity of all qualified primary records.
le dorfier
2009-07-09 04:37:55