Is it possible to write an SQL query that returns the values of something that doesn't exist in another table?

user_14607595 0
Viewed 44

I'm trying to write an SQL query that returns the rows of a table in which certain data of a column does not appear in another table.

For example, let's say I have two tables:

Table 1:

Date             Name              Room
2020/01/23       John              201
2020/01/22       Rebecca           203
2020/01/22       Ronald            204
2020/01/22       Jimmy             205

Table 2 (does NOT have the same amount of columns):

Date             Room
2020/01/22       203
2020/01/23       201
2020/01/22       202
2020/01/22       209

I want to find all the rows in Table 2 in which the room number does NOT show in table 1. Which means my SQL would return

2020/01/22       202
2020/01/22       209

Since room 202 and room 209 does not appear in Table 1.

How would I do this?

mysql sql database
1 Answers

Use not exists:

select t2.*
from table2 t2
where not exists (select 1
                  from table1 t1
                  where t2.room = t1.room
                 );
user_11440350
Related