1
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

closed as off-topic by Paul White, RolandoMySQLDBA, ypercubeᵀᴹ, Mark Storey-Smith, Max Vernon Oct 17 '14 at 14:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, ypercubeᵀᴹ, Mark Storey-Smith, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

2

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 ) 

Not the answer you're looking for? Browse other questions tagged or ask your own question.