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
– RolfBly, Commented Sep 19, 2017 at 19:10

Sergey Kalinichenko · Accepted Answer · 2017-09-19 19:49:28Z

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.

dbajtr · Accepted Answer · 2017-09-18 16:14:58Z

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 );

You could also eliminate the OR by using a UNION, and turning the 2 sub-queries into only one.
Thanks, but from here, I think it's better to use NOT EXISTS, as suggested by @dasblinkenlight.

Collectives™ on Stack Overflow

SQL how to find keys not in use in several tables

2 Answers 2

1 Comment

2 Comments

Hot Network Questions