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.

Your profile

Search

Views

5 ways to return rows from one table not in another table

From Wiki

Jump to: navigation, search

There are at least 5 ways to return data from one table which is not in another table. Two of these are SQL Server 2005 and greater only

First create these two tables

tsqlLine number Off | Hide | Select all

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)

NOT IN

Run the following Code

tsqlLine number Off | Hide | Select all

SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)

What happened? Nothing gets returned! The reason is because the subquery returns a NULL and you can't compare a NULL to anything

Now run this

tsqlLine number Off | Hide | Select all

SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)

That worked because we eliminated the NULL values in the subquery

NOT EXISTS

NOT EXISTS doesn't have the problem that NOT IN has. Run the following code

tsqlLine number Off | Hide | Select all

SELECT *
FROM testjoin j
WHERE NOT EXISTS (
SELECT 1
FROM testnulls n
WHERE n.ID = j.ID
)

Everything worked as expected

LEFT and RIGHT JOIN

Plain vanilla LEFT and RIGHT JOINS

tsqlLine number Off | Hide | Select all

SELECT j.*
FROM
testjoin j
LEFT JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL
SELECT j.*
FROM testnulls n
RIGHT OUTER JOIN testjoin j ON n.ID = j.ID
WHERE n.ID IS NULL

OUTER APPLY (SQL 2005 +)

OUTER APPLY is something that got added to SQL 2005

tsqlLine number Off | Hide | Select all

SELECT j.*
FROM testjoin j
OUTER APPLY (
SELECT id
FROM testnulls n
WHERE n.ID = j.ID
) a
WHERE a.ID IS NULL

EXCEPT(SQL 2005 +)

EXCEPT was added in SQL 2005. It returns everything from the top table which is not in the bottom table.

tsqlLine number Off | Hide | Select all

SELECT * FROM testjoin
EXCEPT
SELECT * FROM testnulls

INTERSECT returns whatever is in both tables (like a regular join).

tsqlLine number Off | Hide | Select all

SELECT * FROM testjoin
INTERSECT
SELECT * FROM testnulls

There seems to be a higher cost associated with the EXCEPT and INTERSECT queries vs. the IN or EXISTS approach due to a sort.

In the case of this test:

tsqlLine number Off | Hide | Select all

DECLARE @TABLE1 TABLE (id int IDENTITY(1,1), name varchar(10))
DECLARE @TABLE2 TABLE (id int IDENTITY(1,1), name varchar(10))
INSERT @TABLE1 VALUES ('DAVE')
INSERT @TABLE1 VALUES ('MARK')
INSERT @TABLE2 VALUES ('DAVE')
INSERT @TABLE2 VALUES (NULL)
SELECT * FROM @TABLE1 t1 WHERE NOT EXISTS (SELECT 1 FROM @TABLE2 t2 WHERE t1.NAME = t2.NAME)
SELECT * FROM @TABLE1
EXCEPT
SELECT * FROM @TABLE2