How to Select Records That Don't Exist in SQL Server

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

How To Select Records that Don't Exist In a Table in SQL?

You don't need a join to do this, Do:

select * from users where userid not in (select userid from orders)

You can use LEFT JOIN also:

SELECT * FROM users
LEFT JOIN orders ON users.userid= orders.userid
WHERE orders.users IS NULL

SQL: Find entries that don't exist

You could use either a LEFT OUTER JOIN or a NOT EXISTS to get the offending records.

LEFT JOIN

SELECT  *
FROM    YourTable yt
        LEFT OUTER JOIN (
          SELECT  EmailID     
          FROM    YourTable
          WHERE   Field_Name = 'Phone'
        ) yte ON yte.EmailID = yt.EmailID
WHERE   yte.EmailID IS NULL

NOT EXISTS

SELECT  *
FROM    YourTable yt
WHERE   NOT EXISTS (
          SELECT  EmailID     
          FROM    YourTable
          WHERE   Field_Name = 'Phone'
                  AND yt.EmailID = EmailID
        ) 

A good read about the difference (similarity) about both methods can be found here

Find records from one table which don't exist in another

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

This is the shortest statement, and may be quickest if your phone book is very short:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatively (thanks to Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

or (thanks to WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

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 WHERE multiple records don't exist

One method uses not exists, twice:

select a.*
from attempts a
where not exists (select 1
                  from attempt_tags att
                  where att.attemptid = a.id and
                        att.tagid = 2
                 ) and
      not exists (select 1
                  from attempt_tags att
                  where att.attemptid = a.id and
                        att.tagid = 11
                 ) ;
  

EDIT:

You can also simplify this to:

select a.*
from attempts a
where not exists (select 1
                  from attempt_tags att
                  where att.attemptid = a.id and
                        att.tagid in (2, 11)
                 ) ;

Select records from a table, which don't exist in another table

Check for NULLs in second table like:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.id = TABLE2.id
WHERE TABLE2.id IS NULL

Alternate solution with NOT EXISTS:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE1.id = TABLE2.id)

One more with NOT IN:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
WHERE TABLE1.id NOT IN(SELECT id FROM TABLE2)

---

Related Topics