Why this SQL is not filtering records

UDA
Enthusiast

Why this SQL is not filtering records

Hello,

What's the difference between the WHERE clause and "AND in Join"?

i.e. What's the difference between Code A and B?

In case of CodeA, the records is not filtered.

CODE A:
--
SELECT *
FROM DP_REDW.V0300_AGREEMENT AS C
LEFT JOIN DP_REDW.V0200_PRODUCT AS D
ON C.Product_Id = D.Product_Id
AND D.Start_Dt = '2008-10-31' )
AND C.Start_Dt = '2008-10-31' )
AND D.HOST_PROD_ID = 'DA'
--

CODE B:
--
SELECT *
FROM DP_REDW.V0300_AGREEMENT AS C
LEFT JOIN DP_REDW.V0200_PRODUCT AS D
ON C.Product_Id = D.Product_Id
AND D.Start_Dt = '2008-10-31' )
AND C.Start_Dt = '2008-10-31' )
Where D.HOST_PROD_ID = 'DA'
--
3 REPLIES
Enthusiast

Re: Why this SQL is not filtering records

Hi,
the main difference is given by join the type. The filtering would behave identically for INNER JOIN and WHERE.

Filtering in ON clause of LEFT JOIN executes conditions separately for table C and table D and then return all filtered rows from C left joined with filtered rows from D allowing NULL for nonexistent D rows.

In the second example including the table D in WHERE condition converts LEFT JOIN effectively into the INNER JOIN.

Except the special cases, the best practice is (I hope :) to place joining condition into the JOIN clause nad filtering condition into the WHERE clause, i.e. JOIN part should be stable, while WHERE is changing.

brgds

Petr

Enthusiast

Re: Why this SQL is not filtering records

Select *
FROM DP_REDW.VX0311_AGREEMENT_ASSET_HIST AS B
LEFT JOIN DP_REDW.V0150_CUSTOMER_ASSET AS C
ON B.Customer_Asset_Id = C.Customer_Asset_Id
And C.Start_Dt = '2008-11-30')
And C.Data_Source_Cd = 'COLL'
And B.Asset_Contract_Role_Cd = 'COLL'
And B.Start_Dt = '2008-11-30')
Where B.customer_asset_id = 412663

Select *
FROM DP_REDW.VX0311_AGREEMENT_ASSET_HIST AS B
LEFT JOIN DP_REDW.V0150_CUSTOMER_ASSET AS C
ON B.Customer_Asset_Id = C.Customer_Asset_Id
And C.Start_Dt = '2008-11-30')
And C.Data_Source_Cd = 'COLL'
And B.Asset_Contract_Role_Cd = 'COLL'
Where B.customer_asset_id = 412663
And B.Start_Dt = '2008-11-30')

What's the difference between CodeA and CodeB? Are they expected to work the same?

I am getting 6 records in CodeA but 1 record in CodeB.

What could be the reason? TableA and TableB in itself returns only 1 record.
Senior Apprentice

Re: Why this SQL is not filtering records

If there's any B.Start_Dt which is not equal to '2008-11-30' a row will be returned in codeA but not in codeB.

All conditions in ON are part of the join condition, but if the join condition is not TRUE the row is still returned because of the Outer Join.
WHERE is processed (logically) after the join.

I'd recommend to read the chapter titled "Outer Join Case Study" in the "SQL Reference Data Manipulation Statements".
It's rephrased from an article in the old "Teradata Review" magazine 10 years ago, "A lesson on Outer Joins learned the hard way" :-)

Dieter