Checking Whether an Item Does Not Exist in Another Table

Checking whether an item does not exist in another table

In general if you want rows that don't exist in another table, then LEFT JOIN the other table and WHERE ... IS NULL to a column on the second table. Also you mentioned that you don't want rows where process.id_string is NULL.

SELECT p.name, p.id_string
FROM
    process p
    LEFT JOIN value_search v
        ON v.id_string = p.id_string
WHERE
    v.id_string IS NULL
    AND p.id_string IS NOT NULL

This is known as an anti-join.

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

select a value where it doesn't exist in another table

You could use NOT IN:

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)

However, meanwhile i prefer NOT EXISTS:

SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)

There are other options as well, this article explains all advantages and disadvantages very well:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

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 '*')

checking if a value exists in another table within the SELECT clause

Using subquery in SELECT CASE will cost more. Use left join instead like below

    select A.name, 
       CASE WHEN B.name IS NOT NULL
       THEN 'common'
       ELSE 'not common'
       END

    from table1 A
    left join table2 B
    on A.name = B.name

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)

Check if entry in table A exists in table B

SELECT *
FROM   B
WHERE  NOT EXISTS (SELECT 1 
                   FROM   A 
                   WHERE  A.ID = B.ID)

---

Related Topics