I have two table tb1 and tb2 . id in tb1 is pk and referenced as fk in tb2. I want to know if tb1 has id values as 1,2,3,4,5 and tb2 has fk_values for 1,2,3,4 but doesn't have for pk 5 how can i find this.
tb1 ------ id ------ 1 ------ 2 ------ 3 ------ 4 ------ while tb2 fk_id ------- 1 -- 1 -- 2 -- 3 -- 3 --
but table 2 doesn't have values for 4 then how can i find out the 4 value here .
Database to be used is mysql.
To find what's in tb1 but not in tb2 do this:
SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.fk_id WHERE tb2.fk_id IS NULL
To do it the other way (in tb2 but not in tb1 ), which cannot be in this case because, there is a foreign key, but none the less it might be useful for you later simply switch the two tables
SELECT tb2.* FROM tb2 LEFT JOIN tb1 ON tb1.id = tb2.fk_id WHERE tb1.id IS NULL
left join .
left join will give you the result that only exists in the left table.which means you must use tb1
left join tb2 like this.
// pick the tb1.id in the result when the fk_id is null. select tb1.id from tb1 left join tb2 on tb1.id = tb2.fk_id where tb2.fk_id is null;