How to Find Rows in One Table That Have No Corresponding Row in Another Table

How to find rows in one table that have no corresponding row in another table

select tableA.id from tableA left outer join tableB on (tableA.id = tableB.id)
where tableB.id is null
order by tableA.id desc 

If your db knows how to do index intersections, this will only touch the primary key index

How to select rows with no matching entry in another table?

Here's a simple query:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null - in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

MySQL: Quickly find rows that do not have a corresponding row in another table

1. Cover a_id field with index in b table
2. Replace WHERE b.id IS NULL with WHERE b.a_id IS NULL

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

Mysql: Select rows from a table that are not in another

If you have 300 columns as you mentioned in another comment, and you want to compare on all columns (assuming the columns are all the same name), you can use a NATURAL LEFT JOIN to implicitly join on all matching column names between the two tables so that you don't have to tediously type out all join conditions manually:

SELECT            a.*
FROM              tbl_1 a
NATURAL LEFT JOIN tbl_2 b
WHERE             b.FirstName IS NULL

Select rows which are not present in other table

There are basically 4 techniques for this task, all of them standard SQL.

NOT EXISTS

Often fastest in Postgres.

SELECT ip 
FROM   login_log l 
WHERE  NOT EXISTS (
   SELECT  -- SELECT list mostly irrelevant; can just be empty in Postgres
   FROM   ip_location
   WHERE  ip = l.ip
   );

Also consider:

• What is easier to read in EXISTS subqueries?

LEFT JOIN / IS NULL

Sometimes this is fastest. Often shortest. Often results in the same query plan as NOT EXISTS.

SELECT l.ip 
FROM   login_log l 
LEFT   JOIN ip_location i USING (ip)  -- short for: ON i.ip = l.ip
WHERE  i.ip IS NULL;

EXCEPT

Short. Not as easily integrated in more complex queries.

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

Note that (per documentation):

duplicates are eliminated unless EXCEPT ALL is used.

Typically, you'll want the ALL keyword. If you don't care, still use it because it makes the query faster.

NOT IN

Only good without NULL values or if you know to handle NULL properly. I would not use it for this purpose. Also, performance can deteriorate with bigger tables.

SELECT ip 
FROM   login_log
WHERE  ip NOT IN (
   SELECT DISTINCT ip  -- DISTINCT is optional
   FROM   ip_location
   );

NOT IN carries a "trap" for NULL values on either side:

• Find records where join doesn't exist

Similar question on dba.SE targeted at MySQL:

• Select rows where value of second column is not present in first column

SQL: Select Rows which does not have a corresponding userID in second table

I believe this will work:

SELECT
    *
FROM
    messages AS m1
    INNER JOIN conversations AS c1 ON m1.conversationid = c1.id
WHERE
    m1.type <> 'Q'
    AND
    m1.userid <> c1.answeruserid

Remember that a JOIN (matching rows) is not the same thing as a WHERE x IN ( SELECT y FROM z ) predicate, even though they can both cause the same results (but only in trivial queries).

Understanding how JOINs work (and when to use them) is one of the hardest parts to grok about SQL. I wish SQL tutorials and guides would introduce subqueries after JOINs, otherwise people get the wrong mental model of SQL.

Selecting Rows That Have One Value but Not Another

To rephrase, I think you mean "I want to return matching rows that have error code 1047 but for which the same values of jobno, no, list do not have a corresponding row with error code 1403"

This part is redundant:

AND   (errorCode = 1047 AND errorCode <> 1403);

If you are saying errorCode must be 1047, you are also saying it is not equal to 1403.

I think you want to select some rows into some result set, then check that there's not another row that disqualifies one of the selected rows from the final result.

So,

SELECT a.job,
       a.date,
       b.group
FROM _log a
  INNER JOIN _active_tmp b
          ON a.jobno = b.jobno
         AND a.no = b.no
WHERE b.list = 'N'
AND   LOGDATE = TO_CHAR(CURRENT_TIMESTAMP,'YYYYMMDD')
AND   a.job NOT LIKE 'HOUSE%'
AND   a.job NOT LIKE 'CAR%'
AND   a.errorCode = 1047
AND   NOT EXISTS (SELECT 1
                  FROM _log c
                    INNER JOIN _active_tmp d
                            ON c.jobno = d.jobno
                           AND c.no = d.no
                  WHERE a.job = c.job
                  AND   a.date = c.date
                  AND   b.group = d.group
                  AND   c.errorCode = 1403)

We select the rows that satisfy the join and have error code 1047 then subtract from that set those rows that also satisfy the join but have error code 1403. You could possibly make this more terse using CTE or a temp table, but this works too.

Note I had to change a few things to make it work in my engine (Postgres), so you may have to change a few things back to Oracle.

---

Related Topics