How to Select Rows With No Matching Entry in Another Table

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.

SQL Join to return results where no match occurs

You didn't say what you mean by "didn't work", but if either of these queries:

SELECT B.* 
 FROM A 
RIGHT JOIN B ON A.KEY = B.KEY 
 WHERE A.KEY IS NULL

SELECT A.* 
 FROM A 
LEFT JOIN B ON A.KEY = B.KEY 
 WHERE B.KEY IS NULL

return no results, it means that every key in the solid table is matched with at least one key in the outer joined table.

If you're trying to find rows in either A or B that have no matching row in the other, you'll need a query like:

SELECT A.*, B.*, CASE WHEN A.KEY IS NULL THEN 'in B but not A' WHEN B.KEY IS NULL THEN 'in B but not A' END as explanation
 FROM A 
FULL OUTER JOIN B ON A.KEY = B.KEY 
 WHERE A.KEY IS NULL OR B.KEY IS NULL

You might hear people saying "but, a key can never be null because that's what a key is for/primary keys cannot be null!" - yes, but we're doing a join; theyre primary keys in the table, but theyre not necessarily primary keys after a join has occurred. The database will be perfectly happy to generate a row full of NULL values for B (including the b.key column) paired with a row full of actual values from A, if there is no row in B where the key is equal to the value of a.key - this means the key of the table is no longer a key of the join, and it's allowed to be null

Could you test on null against another column than key? Yes, but unless that other column in the table is NOT NULLABLE the key column is the best one to use if you're looking for mismatched rows. This is because it's the only column that we can guarantee will contain a null in the situation where the two tables are joined, and there's no data in this table to match with the other table

To better explain, here is some data:

A
key|val
-------
000|hello
001|world

B
key|val
-------
000|hello
002|there

• If these two tables are INNER JOINED, only row 000 will result
• If these two tables are A LEFT JOIN B ON A.KEY=B/KEY WHERE B.KEY IS NULL, row 001 will result
• If these two tables are A RIGHT JOIN B ON A.KEY=B/KEY WHERE A.KEY IS NULL, row 002 will result
• If these two tagbles are full outer joined where a or b key is null, rows 001 and 002 will result

If the key column in either A or B contains NULL, that row will never appear in join results

How can I SUM() the rows with no matching entry in another table?

If you just want the total quantity from all rows in both tables, I suggest using a union query here:

SELECT SUM(qty) AS Total
FROM
(
    SELECT qty FROM tbl_transaction_details
    UNION ALL
    SELECT qty FROM tbl_received_order_details
) t;

Your current approach does not give the results you expect because not every received_details_id from each table matches to one in the other table.

Note that you could also just sum the sums:

SELECT
    (SELECT SUM(qty) FROM tbl_transaction_details) +
    (SELECT SUM(qty) FROM tbl_received_order_details) AS Total;

How to select rows with no match between two tables in SQL?

We can try handling this via the use of an on-the-fly calendar table:

WITH cte AS (
    SELECT DISTINCT t1.plant, t2.country, t1.cost
    FROM t1
    CROSS JOIN t2
)

SELECT
    a.plant,
    a.country,
    a.cost
FROM cte a
WHERE NOT EXISTS (SELECT 1 FROM t1 b
                  WHERE a.plant = b.plant AND
                        a.country = b.country AND
                        a.cost = b.cost);

Demo

Get rows that don't match rows in another table

You could use the not exists operator:

SELECT *
FROM   UsedSlide u
WHERE  NOT EXISTS (SELECT *
                   FROM   LegacySlide l
                   WHERE  u.SlideId = l.SlideId AND u.UserId = l.UserId)

---

Related Topics