SQL how to find keys not in use in several tables

Question

SQL, a table (thesau) with terms that may or may not be in use in several other tables. The other tables point to the id in thesau. I want to find out which terms are NOT in use, i.e. which terms are not referred to by any other table. Simplified:

table: thesau
id term
1  painting
2  sari
4  oil
5  silk
8  gouache

table: object_type
id
1   (-> painting)
7   (-> ... )

table: material_type:
id
2   (-> silk)
4   (-> oil)

in collection, object_type 1 refers to thesau 1 = painting, and so on. Now, I can find thesau terms not in use in one single table, like so:

select distinct thesau.id, thesau.term from thesau_term 
  where thesau_term.id not in 
  (select object_type.id from object_type)

This works. I want to expand the same query to other tables, in one query if possible. In pseudo-code:

select distinct id, term from thesau_term 
  where thesau_term.id not in 
  ((select object_type.id from object_type) or
   (select material_type.id from material_type))

This doesn't work. What am I missing?

@a_horse_with_no_name I'm using MSSQLSMS. Are there different dialects? (Or why do you ask?) — RolfBly, Sep 19 '17 at 19:10

Sergey Kalinichenko · Accepted Answer · 2017-09-19T19:49:28.023

Your current query would list a term that is not in use in at least one other table. If you use AND instead of OR, your query would list terms not in use in any other table:

select distinct id, term from thesau_term 
  where thesau_term.id not in 
  ((select object_type.id from object_type) AND -- <<== HERE
   (select material_type.id from material_type))

You can further simplify your query by using NOT EXISTS

SELECT id, term
FROM thesau_term t
WHERE
    NOT EXISTS (SELECT * FROM object_type ot WHERE ot.id=t.id)
AND NOT EXISTS (SELECT * FROM material_type mt WHERE mt.id=t.id)

or by using an outer join

SELECT DISTINCT t.id, t.term
FROM thesau_term t
LEFT OUTER JOIN object_type ot ON ot.id=t.id
LEFT OUTER JOIN material_type mt ON mt.id=t.id
WHERE ot.id IS NULL and mt.id IS NULL

Thanks for the suggestions, however the first errors `Incorrect syntax near the keyword 'AND'` and `near ')'`. I'd been trying different variations of this, to no avail, before I asked. The third I edited to `select distinct t.id, t.term ...`, to distinguish id and term from the ones in the joined tables, I guess. — RolfBly, Sep 19 '17 at 19:25

score 1 · Answer 2 · answered Sep 18 '17 at 16:14

1

You don't need to use a join, you can do what you have done in your first query, you just need to change the where clause. A not exists would be the most permanent but something like this would work:

SELECT DISTINCT
       id,
       term
FROM thesau_term
WHERE thesau_term.id NOT IN
(
    SELECT object_type.id
    FROM object_type
)
      OR thesau_term.id NOT IN
(
    SELECT material_type.id
    FROM material_type
);

answered Sep 18 '17 at 16:14

dbajtr

2,024
2
14
22

You could also eliminate the `OR` by using a `UNION`, and turning the 2 sub-queries into only one. – easuter Sep 18 '17 at 18:24
Thanks, but from [here](https://stackoverflow.com/a/1703712/1016065), I think it's better to use `NOT EXISTS`, as suggested by @dasblinkenlight. – RolfBly Sep 19 '17 at 19:48

SQL how to find keys not in use in several tables

2 Answers2