SQL : select unmatching records from 2 tables

user_12259719 0
Viewed 52

I have 2 tables and I want to retrieve the rows from the first table where the id_apartment does not appear in the second table:

id | id_floor | id_apartment 
----+----------+--------------
  1 |        0 |          101
  2 |        1 |          101
  3 |        1 |          102
  4 |        1 |          103
  5 |        1 |          104
  6 |        2 |          201
  7 |        2 |          202
  8 |        2 |          203


table2.id | table2.guest | table2.apartment_id
----+---------------+--------------
  1 |         65652 |          101
  2 |         65653 |          101
  3 |         65654 |          101
  4 |         65655 |          101
  5 |         65659 |          102
  6 |         65656 |          201
  7 |         65660 |          202
  8 |         65661 |          202
  9 |         65662 |          202
 10 |         65663 |          203

expected output: 

floor | number
-------+--------
     1 |    103
     1 |    104

I tried using LEFT, INNER and RIGHT join but I always get EMPTY results. How can I manage this?

where-clause join postgresql sql database
5 Answers

You can use not exists:

select *
from table1 t1
where not exists(
    select 1
    from table2 t2
    where t1.id_apartment = t2.apartment_id
)
user_76067640

The canonical solution is not exists:

select t1.*
from table1 t1
where not exists (select 1 from table2 t2 where t2.apartment_id = t1.id_apatment);

However, that would also return the value of 1 in the first table as well as 103 and 104.

I don't know if you want additional filtering on the floor, or if the data is just wrong in the question.

user_11440350

One quick solution would be to use not in

Select * from table_1 where table_1.id_apartment not in (select apartment_id from table_2)
user_40371910

You could use a left anti-join here:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id_apartment = t2.apartment_id
WHERE
    t2.apartment_id IS NULL;

But exists logic should work just as well, and would actually be closer to a literal translation of your requirement.

user_18632290

Let's first tables' name is table1 and second ones' name is table2.

The answer of your question is:

select * from table1 t1 
where t1.id_apartment <> all (select t2.apartment_id from table2 t2 )
user_76612250
Related