Unsure why LEFT JOIN is not returning data-CodePudding

I have a query with sample data below. I am unsure why the first version of the query does not work in SQL Server, but the 2nd query does and would appreciate expertise.

The table contains multiple batches of records.
The queries function is to find the records in the previous batch that don't exist in the current batch, in this case, records with coli1 having values of 2 & 4.

The query where I do a left outer join from the table to a copy of the table does not work.

When I encapsulate the table as sub queries however, it works.

CREATE TABLE #t1 (col1 int,
                  batch int, );

INSERT INTO #t1
VALUES (1, 1),
       (2, 1),
       (3, 1),
       (4, 1),
       (5, 1),
       (1, 2),
       (3, 2),
       (5, 2);


--This query does not work
SELECT *
FROM #t1 a
     LEFT OUTER JOIN #t1 b ON a.col1 = b.col1
WHERE (a.batch = 1
   AND b.batch = 2)
  AND (b.col1 IS NULL);

This returns no results:

col1        batch       col1        batch
----------- ----------- ----------- -----------

This query works as expected

SELECT *
FROM (SELECT * FROM #t1 WHERE batch = 1) a
     LEFT OUTER JOIN (SELECT * FROM #t1 WHERE batch = 2) b ON a.col1 = b.col1
WHERE b.col1 IS NULL;

As it returns this result:

col1        batch       col1        batch
----------- ----------- ----------- -----------
2           1           NULL        NULL
4           1           NULL        NULL

CodePudding user response：

Your first query contains contradictory criteria - if you view the results before filtering you'll see you have no NULL values.

It appears you're trying to find rows where batch = 1 that do not have corresponding rows where batch = 2, which you can do using a self-join with not exists

select * from #t1 t
where t.batch = 1
and not exists (select * from #t1 t2 where t2.col1 = t.col1 and t2.batch = 2);

CodePudding user response：

Since you're using an outer join you need to move the conditions a little:

SELECT *
FROM #t1 AS a
LEFT JOIN #t1 AS b ON a.col1 = b.col1 AND b.batch = 2
WHERE a.batch = 1 AND b.batch IS NULL