Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
NOT IN and NULLs
From Wiki
When you use NOT IN with a table which contains NULL values you can run into a problem First create these two tables
- CREATE TABLE testnulls (ID INT)
- INSERT INTO testnulls VALUES (1)
- INSERT INTO testnulls VALUES (2)
- INSERT INTO testnulls VALUES (null)
- CREATE TABLE testjoin (ID INT)
- INSERT INTO testjoin VALUES (1)
- INSERT INTO testjoin VALUES (3)
- INSERT INTO testjoin VALUES (null)
Now run the following code
- SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)
See what happens? Nothing is returned
Now run this
- SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)
That worked. You can also use NOT EXISTS and LEFT JOIN
LEFT JOIN
- SELECT j.* FROM testjoin j
- LEFT OUTER JOIN testnulls n ON n.ID = j.ID
- WHERE n.ID IS NULL
NOT EXISTS
- SELECT * FROM testjoin j
- WHERE NOT EXISTS (SELECT n.ID
- FROM testnulls n
- WHERE n.ID = j.ID)
Contributed by: --SQLDenis 16:35, 30 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section NULLS
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.