Jeff Moden

---

On my box, the hands down winner is the XML solution with the caveat that it won't handle special characters. The 8K ISNULL function and the XML TYPE solution...

Sure, hint hint. I think it's old ground, though.

http://www.sqlservercentral.com/blogs/tim_mitchell/archive/2008/05/29/using-in-with-possible-null-values.aspx

http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/

http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

SuperDBA-207096 (10/7/2009)

---

Wagner,

You might want to mention in the:

"Excluding the Intersection of the Sets" section this is similar to

select... where not in (select... from table2) but it performs alot better...

David Walker-278941 (10/7/2009)

---

Jeff Moden (10/7/2009)

---

Actually, they ARE becoming required. The use of table names instead of aliases has been deprecated.

Even if they weren't, short table names may work fine...

Yet another article that makes people think that table aliases (t1 and t2) are part of the required syntax. This first Join statement:

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key,...

James Stephens (10/1/2009)

---

As a developer, the best practice here in my opinion, is to wrap all data update/inserts/deletes for those table into a stored procedure with the validation logic. ...

Nadrek (10/1/2009)

---

...With most Start date + End date designs, you can trivially end up with multiple simultaneously overlapping date ranges, and thus, in your example... which promotion is really in...

rja.carnegie (8/31/2009)

---

There could be a role for this IN equivalent (I hope),

WHERE ( col = @value1 OR col = @value2 OR col = @value3 )

in which one or more of...

Yes. It is now clear that your post was about evaluating NULLs in WHERE clauses. I agree that it is correct to use "is NULL" as shown in...

Lynn Pettis (8/24/2009)First, whoever wrote should not have used somecol = NULL. And if you pull the NOT inside the paranes, the change that should be made.

Sorry for the...

Lynn Pettis (8/24/2009)

---

Andy DBA (8/24/2009)

---

rja.carnegie (8/24/2009)

---

Andy DBA (8/18/2009)

Lynn Pettis (8/24/2009)

Andy, only change I'd make to your code is this:

... OR somecol IS NULL) ...

If you then moved the NOT inside...

rja.carnegie (8/24/2009)

---

Wait, isn't that backwards? ...

rja.carnegie (8/17/2009)

IN(...) doesn't seem to mind if a NULL is in there. But NOT (name IN (...) ) apparently also does the same as (name NOT IN (...) )....

John te Lintelo (8/11/2009)

---

Andy DBA (8/10/2009)

---

So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any...

Tim Walker (8/10/2009)

The problem with your view is that with 8 pages of mostly adverse comments, nearly all of which I agree with, this article still has a four star...