Select all values not present in SQL

Question

Currently, my query selects all of the locations specified in my WHERE clause and returns all locations with the provided zip codes in which we made a delivery. My goal is to return all locations with the provided zip codes in which we did NOT make a delivery. There are thousands of zip codes in the database, so NOT is not an option as I need the result constrained to these zip codes. I feel like this is easily possible, but the solution is escaping me.

SELECT ORS.PostDate
,ORS.Name
,ORS.Address
,ORS.City
,ORS.State
,ORS.Zip
,ORS.DriverID
,W.FirstName
,W.LastName
,ORS.RouteID


FROM tblOrderRouteStops ORS WITH (NOLOCK)
INNER JOIN tblWorker W WITH (NOLOCK) ON W.WorkerID = ORS.DriverID

--Select all distinct zips from tblOrderRouteStops, then use where clause to filter them to specified locations.
FULL OUTER JOIN 
(SELECT DISTINCT ORSS.Zip FROM
tblOrderRouteStops ORSS WITH (NOLOCK)) as B
ON B.Zip = ORS.Zip

WHERE ORS.PostDate >= dateadd(day,datediff(day,1,GETDATE()),0)
AND ORS.PostDate < dateadd(day,datediff(day,0,GETDATE()),0)
AND ORS.CustID = 104105
AND ORS.StopType = 1
AND (ORS.Zip = 16635
OR ORS.Zip = 25504
OR ORS.Zip = 28401
OR ORS.Zip = 28803
OR ORS.Zip = 30901
OR ORS.Zip = 31904
OR ORS.Zip = 35801
OR ORS.Zip = 37921
OR ORS.Zip = 38801
OR ORS.Zip = 39232
OR ORS.Zip = 46825
OR ORS.Zip = 49428
OR ORS.Zip = 50265
OR ORS.Zip = 54915
OR ORS.Zip = 67214
OR ORS.Zip = 72205
OR ORS.Zip = 72764
OR ORS.Zip = 72916
OR ORS.Zip = 79605)

Philip Kelley · Accepted Answer

Here's a quick stab at it. I took the original query and modified it, with "before and after" variants based on which AND clause is used at the end.

First, set up a temporary table containing the "target" zipcodes. It will be easier to work with, and SQL will produce better execution plans.

DECLARE @TargetZips table
    (
    Zip  char(5)  not null  --  Replace with the datatype you are using
    )

INSERT @TargetZips values
    (16635)
    ,(25504)
    ,(28401)
    ,(28803)
    ,(30901)
    ,(31904)
    ,(35801)
    ,(37921)
    ,(38801)
    ,(39232)
    ,(46825)
    ,(49428)
    ,(50265)
    ,(54915)
    ,(67214)
    ,(72205)
    ,(72764)
    ,(72916)
    ,(79605)

Then the query:

SELECT
 ORS.PostDate
,ORS.Name
,ORS.Address
,ORS.City
,ORS.State
,ORS.Zip
,ORS.DriverID
,W.FirstName
,W.LastName
,ORS.RouteID


FROM tblOrderRouteStops ORS
 INNER JOIN tblWorker W
  ON W.WorkerID = ORS.DriverID

--Select all distinct zips from tblOrderRouteStops, then use where clause to filter them to specified locations.
 FULL OUTER JOIN (--  Restrict this to the set of zip codes you are intersted in
                  SELECT DISTINCT ORSS.Zip
                   FROM tblOrderRouteStops ORSS
                    INNER JOIN @TargetZips tz
                     on tz.Zip = ORSS.zip
                 ) as B
  ON B.Zip = ORS.Zip

WHERE ORS.PostDate >= dateadd(day,datediff(day,1,GETDATE()),0)
AND ORS.PostDate < dateadd(day,datediff(day,0,GETDATE()),0)
AND ORS.CustID = 104105
AND ORS.StopType = 1

Include this for the original query

AND B.Zip is not null

Include this for the revised query

AND B.Zip is null

Ideally, you have two separate queries. Done that way, you can avoid the FULL OUTER JOIN -- which I recommend, they always make me nervous.

-- Edit ------------------------

The following, IMHO, is kludgey as heck, but it will avoid using an INSERT:

WITH cteTargetZip 
 as (--  Build a table set based entirely on a values clause
     select Zip
      from (values  (16635)
                   ,(25504)
                   ,(28401)
                   ,(28803)
                   ,(30901)
                   ,(31904)
                   ,(35801)
                   ,(37921)
                   ,(38801)
                   ,(39232)
                   ,(46825)
                   ,(49428)
                   ,(50265)
                   ,(54915)
                   ,(67214)
                   ,(72205)
                   ,(72764)
                   ,(72916)
                   ,(79605)
          ) as TargetZips (Zip)
    )
SELECT...

and then replace @TargetZips with cteTargetZips in the main query.

-- Second Edit -----------------------------------------

Ok, this will hopefully do the necessary...

--  Query will return all zip codes in the CTE expression that are not found
--  in your selected delivery locations.  On large tables it will perform
--  poorly, unless there is an index on tblOrderRouteStops.Zip
WITH cteTargetZip 
    as (--  Build a table set based entirely on a values clause
        select Zip
        from (values  (16635)
                    ,(25504)
                    ,(28401)
                    ,(28803)
                    ,(30901)
                    ,(31904)
                    ,(35801)
                    ,(37921)
                    ,(38801)
                    ,(39232)
                    ,(46825)
                    ,(49428)
                    ,(50265)
                    ,(54915)
                    ,(67214)
                    ,(72205)
                    ,(72764)
                    ,(72916)
                    ,(79605)
            ) as TargetZips (Zip)
    )
SELECT cte.Zip
FROM cteTargetZip cte
WHERE not exists (--  The set of delivered-to locations you are interested in
                  --  If every tblOrderRouteStops always has at least one tblWorker,
                  --   you can drop the join.
                    SELECT 1
                    FROM tblOrderRouteStops ORS
                        INNER JOIN tblWorker W
                        ON W.WorkerID = ORS.DriverID
                    WHERE ORS.PostDate >= dateadd(day,datediff(day,1,GETDATE()),0)
                    AND ORS.PostDate < dateadd(day,datediff(day,0,GETDATE()),0)
                    AND ORS.CustID = 104105
                    AND ORS.StopType = 1
                    AND ORS.Zip = cte.Zip  --  Technically, this line makes it a correlated subquery
                    )

Select all values not present in SQL

Answers (2)

Related Questions