CREATE TABLE #temp(i int, flag bit NOT NULL)
INSERT INTO #temp VALUES(1, 1)
INSERT INTO #temp VALUES(2, 0)
INSERT INTO #temp VALUES(NULL, 0)

--returns 2 and NULL
SELECT i FROM #temp WHERE flag=0

--returns 1
SELECT i FROM #temp WHERE flag=1

--you would expect this to return 1, just like the query before, but it returns nothing!
SELECT i FROM #temp WHERE i not in (SELECT i FROM #temp WHERE flag=0)

--this corrected query returns 1, as it should
SELECT i FROM #temp WHERE i not in (SELECT i FROM #temp WHERE flag=0 AND i IS NOT NULL)

Current Music: Лаэртский - Карлица
Tags: code, sql

(3 comments | Leave a comment)