# Unexpected empty result [closed]

``````select a,b from table1
where a not in (select x as a from table2)
AND b not in (select y as b from table3)
``````

The above query gives an empty result. Why? And how should I construct this combined query?

whereas:

``````-- gives a=5,b=6 & a=7,b=8
select a,b from table1
where a not in (select x as a from table2)

-- gives a=1,b=2 & a=5,b=6
select a,b from table1
where b not in (select y as b from table3)
``````

Individually, the queries work; but together they give no result although I think they match row2 from table1

``````table1     table2     table3
a   b      x    y      x   y
1   2      1    0      3   4
5   6      0    0      0   0
7   8      9    5      0   8
``````

I want result of query as:

``````   a  b
5  6
``````

where a is not x in table2 and simultaneously b is not y in table3.

SORRY yes aasim.abdullah the query does work and so does this one

use the following query to get desired result.

select a,b from (select a,b from table1 where a not in (select x as a from table2))temptable where b not in (select y as b from table3)

So my new question is which query first or second has speed performance advantage & whysince my actual table is seriously huge.I am using MYSQL

Your query is correct and it should return one row (5,6)

``````DECLARE @table1 TABLE ( a INT, b INT )
DECLARE @table2 TABLE ( x INT, y INT )
DECLARE @table3 TABLE ( x INT, y INT )

INSERT  INTO @table1
( a, b )
VALUES  ( 1, 2 ),
( 5, 6 ),
( 7, 8 )

INSERT  INTO @table2
( x, y )
VALUES  ( 1, 0 ),
( 0, 0 ),
( 9, 5 )

INSERT  INTO @table3
( x, y )
VALUES  ( 3, 4 ),
( 0, 0 ),
( 0, 8 )

SELECT  a, b
FROM    @table1
WHERE   a NOT IN ( SELECT   x AS a
FROM     @table2 )
AND b NOT IN ( SELECT   y AS b
FROM     @table3 )
``````