Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: bill <billmaclean1@xxxxxxxxx>
- Date: Thu, 6 Nov 2008 02:54:52 -0800 (PST)
I worded that section of my post poorly. I agree that logic dictates
inclusion of the test for NULLs in an outer join with the conditions
in the WHERE clause. The special notation in Oracle is basically
their own syntax that combines the null test with the regular
condition. I don't like it for probably the same reason as you. I
generally don't like "super operators" that do multiple things.
OTOH, I think SQL server tends to carry the "just give the users the
primitives and let him build what he needs" concept too far when it
comes to built in functions. Oracle has a lot of neat functions that
you have to build in SQL server. One of the most useful that comes to
mind is MINUS. Can't think of others at the moment, but I get annoyed
when want to do something that is simple with a built in oracle
function and I have to write a proc with SQL Server. On the whole
though, SQL Server is a fantastic product.
Thanks,
Bill
On Nov 4, 3:28 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
bill (billmacle...@xxxxxxxxx) writes:
As far as I know, SQL Server requires you to include the "and column x
is null" (see my second example) when doing an outer join with the
join conditions in the WHERE clause.
It's not SQL Server that requires you, but the whole logic of it.
FROM JOIN ... builds a table, which is then filered by the WHERE
clause. So if you have "A LEFT JOIN B", and have a condition on a
column in B in the WHERE clause, you are effectively filter out
all rows where B.col has a NULL value. Unless you add that extra
condition.
Oracle has a special notation so you don't have to test for the null
condition, but I don't think SQL 2008 does. Even in Oracle, I don't
like that special notation.
I don't know what Oracle has, but it sounds horrible.
--
Erland Sommarskog, SQL Server MVP, esq...@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
.
- Follow-Ups:
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: Erland Sommarskog
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- References:
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: --CELKO--
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: Ed Murphy
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: --CELKO--
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: bill
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- From: Erland Sommarskog
- Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- Prev by Date: Re: odd SqlInfoMessageEventHandler problem (source code provided)
- Next by Date: Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- Previous by thread: Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- Next by thread: Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
- Index(es):
Relevant Pages
|
Loading
