Unexpected empty result 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 &amp; a=7,b=8
select a,b from table1 
where a not in (select x as a from table2) 

-- gives a=1,b=2 &amp; 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 &amp; 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 )