Re: Examples of real usage of outer joins?

david.karr wrote:
This is more of a general database question, not specific to Java, but
can someone describe an actual realistic use of outer joins? I'm
familiar with how it works, and some trivial examples, but I'm frankly
unsure of why you'd actually need to use it.

Example:

You want a list of all customers and their outstanding orders, but they might not have any orders at the moment:

SELECT c.name, o.ordernum, o.total
FROM customers c LEFT OUTER JOIN orders o
ON c.name = o.cust_name
ORDER BY c.name, o.ordernum;

You don't want to omit a customer from the report just because they haven't ordered something today.

--
Lew
.

Relevant Pages

  • Re: Examples of real usage of outer joins?
    ... can someone describe an actual realistic use of outer joins? ... unsure of why you'd actually need to use it. ... You want a list of all customers and their outstanding orders, ... I wonder if there is anyway simpler and faster than the above that Lew ...
    (comp.lang.java.databases)
  • Re: 3 value logic. Why is SQL so special?
    ... ISNULL(MiddleName, '') AS MiddleName, ... Bad example of ISNULL since it's not required in this case! ... If you don't have a problem with outer joins then it's not too bad, ... FROM Customers C ...
    (comp.databases.theory)
  • Re: Inclusion of Win32 Delphi with .NET Delphi in the future?
    ... > And, as Joe says, customers to be lost. ... > disappointed with this omission. ... I'm still unsure as to how we will be affected - so far we're getting along ...
    (borland.public.delphi.non-technical)