1

I need to fetch data from two tables

For example: I have tables test1, test2. Test1 is the parent table. I need to fetch data is that present in test1 but not in test2. How can I write a query for that?

select id from test1, test2 where test1.id not in (test2.id);
Konerak
  • 39,272
  • 12
  • 98
  • 118
learner
  • 2,609
  • 4
  • 22
  • 23

3 Answers3

4

Do an LEFT OUTER JOIN: this will, for each line in table 1 find the corresponding line(s) in table2 - but the LEFT OUTER will cause it to return the line with NULLs for the table2 values when no values are found. Then, just ask those where the table2 fields are NULL (hence do not exist).

select id from test1
LEFT OUTER JOIN test2 
ON test1.id = test2.id
where test2.id IS NULL

You could try a subquery too, but this will not perform better (usually worse):

SELECT id FROM test1 
WHERE id NOT IN (select id from test2)
Konerak
  • 39,272
  • 12
  • 98
  • 118
  • 2
    http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ – Quassnoi Aug 26 '11 at 12:06
  • @Quassnoi: that's why I said "not better" instead of "worse" ;) – Konerak Aug 26 '11 at 12:17
  • 1
    if `test2.id` is not nullable, the two have identical plans and perform equally (and if it is, the queries have different symantics). What I wanted to say is that your statement is misleading (and the "usually worse" part is just wrong) and feeds the myths about "dreadful subqueries" in `MySQL`. – Quassnoi Aug 26 '11 at 15:04
3
SELECT  id
FROM    test1
WHERE   id NOT IN
        (
        SELECT  id
        FROM    test2
        )
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • This will work but it should be pointed out that JOINs (as in @Konerak 's answer) are usually preferable to nested queries c.f. http://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance – immutabl Aug 26 '11 at 12:06
  • @5arx: this is wrong. http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ – Quassnoi Aug 26 '11 at 12:07
  • Interesting - do you know if is now the case with SQL Server? The article only mentions mySQL. – immutabl Aug 26 '11 at 12:18
  • @5arx: http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ – Quassnoi Aug 26 '11 at 14:57
  • Thanks! That's good to know. But also makes me question a lot of what I *thought* I knew about RDBMS :-) – immutabl Aug 26 '11 at 15:01
1

You can do a subquery, but you could also use a left join:

SELECT test1.*
FROM test1
LEFT JOIN test2
ON test1.id = test2.id
WHERE test2.id IS NULL
Pelshoff
  • 1,464
  • 10
  • 13