Multiple Joining columns on specific condition

Database

Multiple Joining columns on specific condition

Hi all,

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. 

example:

Table 1:

Col1    Col2    Col3    Col4 .......

A1      B1       C1       D1

A2      B2       C2     D2

A3     B3       C3       D3

A2     B2       C3       D2

A3     B4       C4       D4       

Table 2:

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.

2 REPLIES
Senior Apprentice

Re: Multiple Joining columns on specific condition

You can simply code it like

SELECT *
FROM t1
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...

Re: Multiple Joining columns on specific condition

Thanks Dieter...This is not a one time operation. This has to be performed every weekend.