CTE left join not giving me correct result

Question

CTE left join not giving me correct result

datsun

SSCrazy

Points: 2397
More actions
May 21, 2020 at 1:11 pm
Go to Answer
#3756558
Hello,
Why is the Left Join with a CTE giving me wrong results? I want to know which Emails are not in Table B using their UserIDs
Instead it's giving me all the records from CTE and Null values from the Table B. What am I doing wrong?
Many thanks.
```
; With CTE as (
select distinct userID, email from emailcommunications
where userID is not null
and  email IN (
'abc@noemail.com','xyz@noemail.com','wer@noemail.com','lkj@noemail.com','zza@noemail.com','sadd@noemail.com')
)
select * from CTE left join tableB b on (CTE.userID = b.userID )
and b.userID is null;
```
frederico_fonseca

SSCoach

Points: 16154
More actions
May 21, 2020 at 2:03 pm
Answer
#3756576

or keeping the CTE change "and b.userID is null;" to "WHERE b.userID is null;"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Try switching the order of CTE and tableB in your LEFT JOIN. Or leave as is and change to a RIGHT JOIN.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Or use an alternative query:

SELECT DISTINCT ec.userID, ec.email 
FROM emailcommunications ec
WHERE ec.email IN ('abc@noemail.com','xyz@noemail.com','wer@noemail.com','lkj@noemail.com','zza@noemail.com','sadd@noemail.com')
AND NOT EXISTS (SELECT 1 FROM tableB b WHERE b.userID = ec.userID)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

datsun SSCrazy Points: 2397 More actions · Answer 3

Thanks frederico_fonseca.

That worked. I guess "and" clause is part of the left join (even after the brackets) and "where" clause is the final filter applied in the CTE join.

This reply was modified 4 years, 6 months ago by datsun.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 4

You have parenthesis, not brackets in the original query. Not to be pedantic, but we can get confused if we're not all using the same nomenclature.

The WHERE clause is applied alter, after joins, but must include the WHERE keyword. You have this:

from CTE left join tableB b on (CTE.userID = b.userID )
and b.userID is null;

The parenthesis do group things, but only for the purposes of AND/OR issues. This doesn't separate out the b.usedID IS NULL out of the ON clause. You'd need this if you wanted different behavior

from CTE left join tableB b on (CTE.userID = b.userID )
WHERE b.userID is null;