CODEWITHSUNDEEP
sqlsql-server
user3452099
user3452099

Reputation: 1

SQL - select multiple records which only exist in multiple tables

I have some 20+ tables consisting of 20,000+ records each, I wish to query for only record values which exist in all tables. I'm not sure if there was a different approach than group all together with UNION?

Example:

Table_A:    1,2,6,9,12,15
Table_B:    2,6,11,15,18,26
Table_C:    6,12,14,15,23,24
Table_D:    3,4,5,6,8,15

Result: Only 6 & 15 would return!

Required Result:

Only values which appear in all tables (to be inserted into new table!)

Many thanks!

Upvotes: 0

Views: 658

Answers (2)

M.Ali
M.Ali

Reputation: 69524

SELECT *
FROM TABLE_A A INNER JOIN TABLE_B B
ON A.Column_R = B.Column_R
INNER JOIN TABLE_C C
ON B.Column_R = C.Column_R
INNER JOIN TABLE_D D
ON C.Column_R = D.Column_R

Column_R would be the referencing column between these tables.

Or another approach could be using EXISTS operator something like this...

SELECT A.*
FROM TABLE_A A
WHERE 
     EXISTS (SELECT 1
             FROM TABLE_B
             WHERE Column_R = A.Column_R)
AND  EXISTS (SELECT 1
             FROM TABLE_C
             WHERE Column_R = A.Column_R)
AND  EXISTS (SELECT 1
             FROM TABLE_D
             WHERE Column_R = A.Column_R)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The right expression is intersect, not union:

select a.* from table_a a
intersect
select b.* from table_b b
intersect
select c.* from table_c c
intersect
select d.* from table_d d;

Not used very often, but exactly what you want. And, it works with multiple columns.

Upvotes: 2

Related Questions