14 September 2007 08:22
Alex_Kuznetsov
Not qualifying column names with table aliases may lead to hard-to-find errors
CREATE TABLE #t1(i INT)
INSERT #t1 VALUES(1)
INSERT #t1 VALUES(2)
CREATE TABLE #t2(i INT)
INSERT #t2 VALUES(1)
INSERT #t2 VALUES(3)
-- incorrect result
SELECT i FROM #t1
WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE #t2.i = i)
/*
i
-----------
(0 row(s) affected)
*/
-- correct result
SELECT i FROM #t1 t1
WHERE NOT EXISTS(SELECT 1 FROM #t2 WHERE #t2.i = t1.i)
/*
i
-----------
2
(1 row(s) affected)
*/