All Forums Database
18 Feb 2015
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.
 

dnoeth 4628 posts Joined 11/04
18 Feb 2015

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...
 
 

Dieter

18 Feb 2015

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

You must sign in to leave a comment.