Re: Sybase/Sql server Outer Joins, nulls, and counts

 (dba_222_at_yahoo.com) writes:
> Hmm. The logic was all laid out in the examples in the first posting.
> It is the same logic that has worked perfectly hundreds,
> probably thousands, of times for me before.
>
> Oracle would not let such a statement even compile.
> (can't have an AND without first having a WHERE)
> The compiler would give an error. In which case, I would have just
> corrected the syntax and got on with it.
>
> The sql server/Sybase compilers allowed the bad syntax.
> Thus the statement:
> If only the SQL compiler would catch the bad syntax. :(
 

No, it is not bad syntax, it is just that you have not understood how ANSI joins work. Since this syntax works on Oracle too, as far as I know, there is good reason to migrate to that syntax for Oracle as well.

When you say

   A LEFT JOIN B ON a.col = b.col AND b.othercol = 2

This means that you include all rows from table A. In the columns for table B, all are NULL where othercol has another value than 2, as well as those rows there no row in B match A.

This gives a new virtual table what can be filterted with WHERE.

I don't know about Oracle's proprieraty syntax, but SQL Server and Sybase has an old syntax *=, which in SQL 2005 is only available in compatibility mode. (I don't know about Sybase.) And for a very good reason. The *= and =* operators were ambiguous, and the result you got was often confusing. And there was no *=* so full joins were not possible.

-- 
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx