How to Correctly Retrieve Missing Data Using `SELECT * WHERE NOT EXISTS`?

Retrieve Missing Data Using "SELECT * WHERE NOT EXISTS"

Problem:

To retrieve data from a table where certain cells are not present in another table, a user implemented the following query:

SELECT * from employees WHERE NOT EXISTS (SELECT name FROM eotm_dyn)

However, this query returns no results, despite known missing data.

Solution:

To address the issue, it is necessary to join the two tables based on a common key, such as employeeID. Using NOT EXISTS without a join will always yield no results if the second table contains any data.

The correct query, assuming employeeID is the joining key, is:

SELECT  *
FROM    employees e
WHERE   NOT EXISTS
        (
        SELECT  null 
        FROM    eotm_dyn d
        WHERE   d.employeeID = e.id
        )

Alternatively, a less efficient approach would be to use a LEFT JOIN and filter out the NULL values.

The above is the detailed content of How to Correctly Retrieve Missing Data Using `SELECT * WHERE NOT EXISTS`?. For more information, please follow other related articles on the PHP Chinese website!