Joining onto NULL if Record does not exist SQL

Question

I have a query that selects customers from a table CustomerDetails, and left joins onto another table (CustomerActivity) to get their last login time and finally left joins onto another table (OpenOrderDetails) to get their last open order (if applicable). I also have a big WHERE clause filtering this data

A customer can only have one record in the OpenOrderDetails table at anytime. My query looks like the following:

SELECT CD.*, H.LastCustomerLoginTime, OD.OrderFiledDate, OD.OrderCompletedDate FROM CustomerDetails CD LEFT JOIN CustomerActivity H ON H.CustomerID = CD.CustomerID LEFT JOIN OpenOrderDetails OD ON CD.CustomerID = OD.CustomerID WHERE CD.OrderStatus IN (1,2,3) AND (CustomerType = 1 or (CustomerType = 3 and CustomerActive IN (1,2))) AND (OD.OrderFiledDate IS NULL OR CD.TimeStamp >= OD.OrderFiledDate) AND (OD.OrderCompletedDate IS NULL OR CD.TimeStamp <= OD.OrderCompletedDate)

My issue is that this query only returns customer records that have a record in the OpenOrderDetails table. How do I return every customer, and OrderFiledDate/OrderCompletedDate if present, and NULL if a record for that customer does not exist in the OpenOrderDetails table?

From what table(s) are: fields customerType and CustomerActive? This is a common order of operations issue. You want the filters on tables being left joined to occur as part of the join. If they occur in the where clause the LEFT join then behaves like an inner join. Logically the tables are joined. The where clause then eliminates values not matching the criteria; including NULLS generated by the left join. The is null check on OD is a less common but acceptable approach to keeping the values from the left join, which is why I asked about CustomerActive and CustomerType. — xQbert
– xQbert, Commented Sep 26, 2017 at 16:29
*There's potentially more to this question than a simple where clause limit. I'd guess that one or both customerType or CustomerActive isn't in customerDetails. and it too would need to be moved into the join or have the is null check. — xQbert
– xQbert, Commented Sep 26, 2017 at 16:38
what is DLECS.CustomerID on the 2nd left join? Shouldn't it be OD.CustomerID? Alias/table DLECS doesn't exist in the query as defined. — xQbert
– xQbert, Commented Sep 26, 2017 at 16:41
@xQbert, it's a typo, it should be CD. based on your question comments I agree with your concern, but selected answer tells a different story. this question looks suspicious to me at all ;) — FLICKER
– FLICKER, Commented Sep 26, 2017 at 16:46

Jason A. Long · Accepted Answer · 2017-09-26 16:31:12Z

When doing a LEFT JOIN, referencing ANY of the right side table columns in the WHERE clause will turn it into an INNER JOIN. To get around this, simply remove any and all predicates that reference "right side" columns from the WHERE clause and move them to the LEFT JOIN condition.

Something along these lines...

SELECT CD.*, H.LastCustomerLoginTime, OD.OrderFiledDate, OD.OrderCompletedDate FROM CustomerDetails CD LEFT JOIN CustomerActivity H ON H.CustomerID = CD.CustomerID LEFT JOIN OpenOrderDetails OD ON CD.CustomerID = DLECS.CustomerID AND ( OD.OrderFiledDate IS NULL OR CD.TimeStamp >= OD.OrderFiledDate ) AND ( OD.OrderCompletedDate IS NULL OR CD.TimeStamp <= OD.OrderCompletedDate ) WHERE CD.OrderStatus IN ( 1, 2, 3 ) AND ( CustomerType = 1 OR ( CustomerType = 3 AND CustomerActive IN ( 1, 2 ) ) );

Clarification: When doing a LEFT JOIN, referencing ANY of the right side table columns in the WHERE clause without allowing for NULLs will turn it into an INNER JOIN.
@HABO - technically... yes you are correct... but there's no need to confuse someone, who's trying to learn, with odd-ball syntax.

TriV · Accepted Answer · 2017-09-26 16:25:37Z

Just move some OpenOrderDetails conditions to join clause

 SELECT CD.*, H.LastCustomerLoginTime, OD.OrderFiledDate, OD.OrderCompletedDate FROM CustomerDetails CD LEFT JOIN CustomerActivity H ON H.CustomerID = CD.CustomerID LEFT JOIN OpenOrderDetails OD ON CD.CustomerID = DLECS.CustomerID AND (OD.OrderFiledDate IS NULL OR CD.TimeStamp >= OD.OrderFiledDate) AND (OD.OrderCompletedDate IS NULL OR CD.TimeStamp <= OD.OrderCompletedDate) WHERE CD.OrderStatus IN (1,2,3) AND (CustomerType = 1 or (CustomerType = 3 and CustomerActive IN (1,2)))

While I agree this is easier to read/follow. I don't see how it will change the results. the is null check of OD fields and use of OR would keep the values from the left join. So why would this work and user1501171 not?
Gotta admit I'm shocked this worked and your original query didn't @user1501171. I don't see where DLECS comes from...

Collectives™ on Stack Overflow

Joining onto NULL if Record does not exist SQL

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related