CODEWITHSUNDEEP
mysql
learner
learner

Reputation: 2789

How to fetch data from two related tables filter by one table?

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);

Upvotes: 1

Views: 681

Answers (3)

Pelshoff
Pelshoff

Reputation: 1464

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425361

SELECT  id
FROM    test1
WHERE   id NOT IN
        (
        SELECT  id
        FROM    test2
        )

Upvotes: 3

Konerak
Konerak

Reputation: 39763

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)

Upvotes: 4

Related Questions