Using NOT Exists in SQL

[jeffpwhite]

I'm not sure if this is the best approach or not but I have a query
that returns about 28k records. In part there is a join that reads:

inner join EARoutAL on earoutings.id = earoutal.routid and
earoutings.routtype in (3,4,5)

This works fine. However it was discovered that there are many, about
6k records that have NO ROUTTYPE. So I was thinking of just adding the
NOT Exists to the end but I'm either very wrong in my thinking or I
have the syntax wrong. My luck, it will be both! So, I'm looking to
return all records that have a routtype of 3, 4 and 5 and those records
that do not have a routtype at all. Any suggestions?

 

[HSalim[MVP]]

Since the filtering is happening on the join clause, this could get tricky.
First move filter to a where clause
WHERE earoutings.routtype in (3,4,5) or earoutings.routtype is null
If that does not work, you will have to use a subquery.

 

[RB Smissaert]

How about:

inner join EARoutAL on
(earoutings.id = earoutal.routid)
where
earoutings.routtype in (3,4,5) or
earoutings.routtype is NULL

RBS