I have the below case. Table 1 has around billion rows and Table 2 has around 10000 rows.
I need to filter out the rows from table 1 which are present in table 2.
Col1 Col2 Col3 Col4 .......
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A2 B2 C3 D2
A3 B4 C4 D4
Col1 Col2 Col3 Col4
A1 null null null
null B2 C2 null
null null null D3
Here from Table 1 except last two rows remaining should be eliminated. So the requirement here is I need to check for the combination of not null columns from table 2 to eliminate the corresponding combination from Table 1. The combination in Table 2 can be anything with all the four fields as not null or 3 not null fields and one null field etc......
Can anyone please suggest a way to implement this.
You can simply code it like
WHERE NOT EXISTS
( SELECT * FROM t2
WHERE t1.col1 = coalesce(t2.col1, t1.col1)
AND t1.col2 = coalesce(t2.col2, t1.col2)
but this will result in a CROSS join.
Is this a one time operation?
Otherwise you should fix your data model...