Say I have two tables
First_table
| id | occupation |
|---|---|
| efg | carpenter |
| hjk | teacher |
| moo | scientist |
| dss | engineer |
Second_table
| id | state |
|---|---|
| efg | PA |
| loi | DE |
| moo | NY |
| nbw | MD |
Now I want to write a query that gets rid of the rows of the first table, if first_table.id is in second_table.id. So the output would be
| id | occupation |
|---|---|
| hjk | teacher |
| dss | engineer |
One way I could do this is by writing a where clause, and then put parameters into the where clause such as
where first_table.id != moo and first_table.id != efg
but that would require me to write some logic to figure out which data to exclude, and I would want all the logic to be in a query anyways.
CodePudding user response：
This sounds like not exists:
select f.*
from first_database f
where not exists (select 1 from second_database s where s.id = f.id);