T-Sql: How to Select Values in Value List That Are Not in the Table

T-SQL: How to Select Values in Value List that are NOT IN the Table?

For SQL Server 2008

SELECT email,
       CASE
         WHEN EXISTS(SELECT *
                     FROM   Users U
                     WHERE  E.email = U.email) THEN 'Exist'
         ELSE 'Not Exist'
       END AS [Status]
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  

For previous versions you can do something similar with a derived table UNION ALL-ing the constants.

/*The SELECT list is the same as previously*/
FROM (
SELECT 'email1' UNION ALL
SELECT 'email2' UNION ALL
SELECT 'email3' UNION ALL
SELECT 'email4'
)  E(email)

Or if you want just the non-existing ones (as implied by the title) rather than the exact resultset given in the question, you can simply do this

SELECT email
FROM   (VALUES('email1'),
              ('email2'),
              ('email3'),
              ('email4')) E(email)  
EXCEPT
SELECT email
FROM Users

Select values that are not included on a table

Your method is conceptually okay. But there is a problem if Customers.CellId is ever NULL. If that is the case, then the query will return no rows.

For this reason, I recommend never using NOT IN with a subquery. Instead, use NOT EXISTS:

SELECT c.Cell_ID
FROM Cells as c
WHERE NOT EXISTS (SELECT 1
                  FROM Customers as cu
                  WHERE c.Cell_ID = cu.CellID
                 );

SELECT those not found in IN() list

You can use a derived table or temporary table for example to hold the list of CustomerId then find the non matching ones with EXCEPT.

The below uses a table value constructor as a derived table (compatible with SQL Server 2008+)

SELECT CustomerId
FROM   (VALUES(1),
              (79),
              (14),
              (100),
              (123)) V(CustomerId)
EXCEPT
SELECT CustomerId
FROM   Customers 

Select values from a list that don't exist in DB Table

An outer join should do it:

with var_nos (no) as ( values ('Z1234'), ('Z1235'),  ('Z1236'))
select no 
from var_nos
left outer join 
db_table
  on db_table.db_idno = var_nos.no
where db_table.db_idno is null

Alternatively:

select no 
from table ( values ('Z1234'), ('Z1235'),  ('Z1236')) as var_nos (no)
where not exists (
  select 1 from db_table where db_table.db_idno = var_nos.no
)

P.S. Not tested.

Select values from a table that are not in a list SQL

Depending on how many values you have, you could do a few unions.

See: http://www.sqlfiddle.com/#!5/0e42f/1

select * from (
  select 'Test 1' thename union
  select 'Test 2' union 
  select 'Test 3'
)
where thename not in (select name from foo)

Return the values that do not exist in the table but do exist in my IN list?

Constructing an IN statement with 8,366 different values is not efficient. This essentially results in 8,366 different OR statements in your query.

For something like this, I recommend using a temp table and inserting your values into that, then using a JOIN to it. In this specific case, you should use a LEFT JOIN to that table and take only those values that are not found.

For example:

Declare @Numbers Table (Number Varchar (20));

Insert  @Numbers
Values  ('1111111111111111111'),
        ('2222222222222222222'),
        ('3333333333333333333'),
        ...

Select      N.Number
From        @Numbers    N
Left Join   Device      D   On  N.Number = D.Number 
Where       D.Number Is Null

You can also use a CTE to build your list of numbers as well:

;With Numbers (Number) As
(
    Select  '1111111111111111111' Union All
    Select  '2222222222222222222' Union All
    Select  '3333333333333333333' Union All
    ...
)
Select      N.Number
From        Numbers     N
Left Join   Device      D   On  N.Number = D.Number 
Where       D.Number Is Null    

How can I select from list of values in SQL Server

Simplest way to get the distinct values of a long list of comma delimited text would be to use a find an replace with UNION to get the distinct values.

SELECT 1
UNION SELECT 1
UNION SELECT 1
UNION SELECT 2
UNION SELECT 5
UNION SELECT 1
UNION SELECT 6

Applied to your long line of comma delimited text

• Find and replace every comma with UNION SELECT
• Add a SELECT in front of the statement

You now should have a working query

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

Query to get items which are in list and not in list

the solution below uses a recursive cte (cte_AllPieceNo) to generate a list of possible PieceNo for each Items

From there, just use that and check for NOT EXISTS() in tbl_ItemPieces

; with
cte_AllPieceNo as                 -- Added this
(
    select  Id, TotalPieces, PieceNo = 1
    from    tbl_Items
    union all
    select  Id, TotalPieces, PieceNo = PieceNo + 1
    from    cte_AllPieceNo
    where   PieceNo < TotalPieces
)
SELECT   
    a.ItemNo, 
    COUNT(b.PieceNo) ActualPieces, 
    a.TotalPieces,
    STUFF(( SELECT  ', ' + CAST( PieceNo as varchar(50) )
            FROM    tbl_ItemPieces b 
            WHERE   b.ItemId = a.Id
            FOR XML PATH('')), 1, 2, '') as AvailablePieces,
    STUFF(( SELECT  ', ' + CAST( c.PieceNo as varchar(50) ) -- added this
            FROM    cte_AllPieceNo c
            WHERE   c.Id    = a.Id
            AND     NOT EXISTS
                    (
                        SELECT  *
                        FROM    tbl_ItemPieces d
                        WHERE   d.ItemId    = c.Id 
                        AND     d.PieceNo   = c.PieceNo
                    )
            FOR XML PATH('')),1,2, '') as NotAvailablePieces
FROM    tbl_Items a
        INNER JOIN tbl_ItemPieces b on a.Id = b.ItemId
GROUP BY a.ItemNo, 
         a.TotalPieces, 
         a.Id

if you have a tally table, you can use that to replace the recursive cte

Here is the section of the code that uses tally table.

cte_AllPieceNo as
(
    select  Id, PieceNo = n
    from    tbl_Items
            cross join tally
    where   n >= 1
    and     n <= TotalPieces
)

---

Related Topics