CODEWITHSUNDEEP
sql
storm97
storm97

Reputation: 43

How to find out if a row of one table exists in the values of at least one row of another table?

I have two SQL tables, example below:

Table 1 (column types varchar, integer, numeric)

A B C D
A007 22 14.02 _Z 1
A008 36 15.06 _Z 1

Table 2 (column types varchar)

A B C D
A009,A010,A011 33,35,36 16.06,17.06 _Z 1,_Z 2
A003,A007,A009 14,22,85 13.01,17.05,14.02 _Z 1

Is there a way to compare individual rows of the first table with the rows of the second table and find out which row of the first table does not occur in the values of any row of the second table?

As can be seen, the first row of table 1 occurs in the values of the second row of table 2.

However, the second row of table 1 does not occur in the values of the rows of table 2, therefore the desired output is row 2 of table 1.

Desired output table:

A B C D
A008 36 15.06 _Z 1

What I have tried so far:

My solution was to create a table containing all possible combinations of column values for each row of the second table (with the same column data types as the columns of the first table) and then use SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2 to get the difference rows.

The solution worked (for relatively small tables) but I am currently in a situation where generating all combinations of column values for each row of the second table (which in my case has 500 rows) results in a table containing millions of rows, so I am looking for another solution, where I can use the original table with 500 rows.

Thank you in advance for any possible answer, preferably one that could also work in the IBM DB2 database.

Upvotes: 0

Views: 222

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

We can use a LIKE trick here along with string concatenation:

SELECT t1.*
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table2 t2
    WHERE ',' || t2.A || ',' LIKE '%,' || t1.A || ',%'
);

Note that it would be a preferable table design for Table2 to not store CSV values in this way. Instead, get every A value onto a separate row.

Upvotes: 1

Related Questions