Re: Left Join vs Right JOIN


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9C2A742344FBBYazorman@xxxxxxxxxxxx
Hugo Kornelis (hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx) writes:
You usuallly should not need these types of join much. Most joins would
be the default "inner" join, written as

Yes and no. The need for outer joins are certainly very common. But I
sometimes come across queries that goes:

SELECT ..
FROM tbl
LEFT JOIN ...
LEFT JOIN ...
LEFT JOIN ...
LEFT JOIN ...
LEFT JOIN ...

Then it is a good guess that someone is using LEFT JOIN without thinking
of what it means, of whether that is the right thing.

The end result will be a result set with all CustomerDetails, along with
their Customer details (if he/she exists and is known), and the
Department details for that customer (if it exists and is known).
...
But as memorized before - in most shops, you'd expect the appropriate
FOREIGN KEY constraints on the cd.CustomerID and the d.Department
columns, and you'd further simplify this to

SELECT * -- Never use SELECT * in production code!!!
FROM CustomerDetail AS cd
INNER JOIN Customer AS c ON cd.CustomerID = c.CustomerID
INNER JOIN Department AS d ON c.DepartmentID = d.DepartmentID;

Here you make the important assumption that Customers.DepartmentID is
not nullable. We don't know the business, but it seems conceivable that
customers may not be associated with a department. In that case, there
is an important difference between an inner join and an outer join here.

Now, there is an important question.

If a field in an outer JOIN is not nullable, but because of the OUTER JOIN
would normally be a null if the criteria didn't match. What happens there?

Thanks,

Tom

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx



.

Relevant Pages

  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Relational question
    ... If one considers the question as purely a SQL question then, ... the two queries are equivalent but with further explanation ... Note that if this were an INNER JOIN we would only need to ... >> left outer join ...
    (microsoft.public.sqlserver.programming)
  • Re: Left Join vs Right JOIN
    ... The need for outer joins are certainly very common. ... Department details for that customer. ... INNER JOIN Customer AS c ON cd.CustomerID = c.CustomerID ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Expression question
    ... Invest about 10 minutes of time with this query, ... Choose View / SQL from the main menu ... Is there an "Outer Join for Dummies" ... >> per customer in Northwind, you could use the following query. ...
    (microsoft.public.access.queries)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... "SQL Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... Even if I were to bypass the Hibernate generated SQL and roll my own, that did not work at all, it returned all rows unfiltered. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
Quantcast