Query to check the consistency of records

Question

I have four tables

TableA:

• id1
• id2
• id3
• value

TableB:

• id1
• desc

TableC:

• id2
• desc

TableD:

• id3
• desc

What I need to do is to check if all combinations of id1 id2 id3 from table B C and D exist in the TableA. In other words, table A should contain all possible combinations of id1 id2 and id3 which are stored in the other three tables.

Answer 1

This query evaluates all combinations of id1, id2 and id3 (the cross join) and finds which combinations are not present in table a.

select b.id1, c.id2, d.id3 from
 TableB b cross join TableC c cross join TableD d WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3=d.id3)

EDIT: With an RIGHT JOIN

SELECT allPerms.id1, allPerms.id2, allPerms.id3 FROM a RIGHT JOIN (select b.id1, c.id2, d.id3 from
 TableB b cross join TableC c cross join TableD) allPerms
 ON a.id1=allPerms.id1 AND a.id2=allPerms.id2 AND a.id3=allPerms.id3
 WHERE a.id1 IS NULL

The two are pretty much the same. Since we are not actually fetching values from the joined table, some people prefer the first approach, since it captures the intent and spirit of the query. The second version is more "implementation oriented". A good optimizer will produce an efficient plan for both, but on some lesser RDBMSs, the second version will run faster.

With a predefined set for table D - id3 has values (2,5,6)

select b.id1, c.id2 from
 TableB b cross join TableC c WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3 IN (2,5,6))

But, this doesn't give you the id3 that is missing in the table A row. For that, I think the simplest is to emulate the table via a union, e.g.

select b.id1, c.id2, d.id3 from
 TableB b, TableC c, (select 2 id3 union select 5 union select 6) d 
WHERE NOT EXIST
 (select 1 FROM TableA a WHERE a.id1=b.id1 AND a.id2=c.id2 AND a.id3=d.id3)

(This is still using cross join - it's implied if tables are separated by commas.)