Two SQL Queries to find rows present in one table but not another.

Sometimes when querying SQL tables, you wish to find rows that are missing as opposed to the rows which are present. In this post, I’ll demonstrate 2 queries you can use to determine rows that are present in one table but missing in another table…

Link to photagrapher.
Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Note: For the example queries in this post I am using the Oracle Database. Your specific SQL implementation may have something comparable or completely different to the queries I use.

I have 2 simple tables, each having a single column of the CHAR datatype, that I’m using for the sample data:

SELECT * FROM TAB1;

Consider making a donation as I continue to provide valuable content here on my blog. Thank you so much!!! Every bit is much appreciated and helps tremendously!

SELECT * FROM TAB2;

The desired outcome is to write a query that returns ‘L’ and ‘O’ because those rows are not present in the TAB2 table.

Query 1 to find missing rows: MINUS Set Operator

MINUS is Oracle’s equivalent of the EXCEPT set operator. MINUS returns only those rows that are present in the 1st SELECT statement that is not found in the 2nd SELECT statement, also removing duplicates from the result set.

We can use MINUS and easily find out which rows are in table TAB1 that are not in table TAB2. The 1st SELECT should be from TAB1 as that is the query MINUS keeps any rows from not found in the 2nd SELECT.

SELECT * FROM TAB1
MINUS
SELECT * FROM TAB2;

(Pro Tip: Remember that with set operators, such as MINUS, column count and datatype should match for the involved SELECT statements.)

The returned rows have COL1 values of ‘L’ and ‘O’ which are not present in table TAB2.

Code, content, and community for developers.

The LAMP stack and the PHP technologies and frameworks it runs.

Query 2 to find missing rows: LEFT JOIN, filter on NULLS

A LEFT OUTER JOIN returns all rows from the left table and any matching rows from the right table. Where there is not a match in the right table, a LEFT OUTER JOIN returns NULL. Let’s visit an example query for full comprehension:

SELECT T1.COL1 T1_COL, T2.COL1 T2_COL
FROM TAB1 T1
LEFT JOIN TAB2 T2
ON T1.COL1 = T2.COL1;

Since there is no match for TAB1 rows of ‘L’ and ‘O’ in TAB2, NULL is returned on the TAB2 side of the JOIN. With that knowledge, we can filter that side of the result set, keeping only those rows where the TAB2 column COL1 value is NULL.

SELECT T1.COL1 LJ_C1
FROM TAB1 T1
LEFT JOIN TAB2 T2
ON T1.COL1 = T2.COL1
WHERE T2.COL1 IS NULL;

(Pro Tip: Remember, NULL is not equivalent to anything. Not even another NULL. Therefore, equality tests do not work when filtering by NULL and will return an empty set. You must use IS NULL when filtering NULL. Or the converse, IS NOT NULL, if the logic dictates such.)

Again, the rows with values ‘L’ and ‘O’ are returned as desired.

Queries to find missing rows: Reader Suggestions

Now it’s your turn. What other queries can be used to achieve these results that you would use? Tell me all about them in the comments section below.

As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated.

Further Reading

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.



To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

2 thoughts on “Two SQL Queries to find rows present in one table but not another.”

  1. Josh,
    this post got me thinking, and I believe that a FULL OUTER JOIN might come in handy if you want to list records that are missing from either table.
    This is something I do a lot when regression testing changes.
    Reading your post made me realise that this can be accomplished like this.
    Apologies if the formatting below is a bit off. I’ve tried to use “PRE” tags but I’m not sure how they render in comments on this site.
    First, let’s add some records to t2 which aren’t in T1 :
    insert into t2 values(‘P’);
    insert into t2 values(‘Q’);
    insert into t2 values(‘R’);
    commit;
    To list only the records missing from either table :
    select
    coalesce(t1.col1, t2.col1) as key_value,
    nvl(t1.col1, ‘Missing from t1’) as t1_col,
    nvl(t2.col1, ‘Missing from t2’) as t2_col
    from t1
    full outer join t2
    on t1.col1 = t2.col1
    where ( t1.col1 is null or t2.col1 is null)
    order by 1
    /
    K T1_COL T2_COL
    – ————— —————
    L L Missing from t2
    O O Missing from t2
    P Missing from t1 P
    Q Missing from t1 Q
    R Missing from t1 R
    Thanks for helping me to realise that there was an easier way to do this than I had been using for more years than I’d care to admit !
    Mike

    Reply

    • Awesome example Mike! Thanks so much for commenting and taking the time to share this example here. The FULL OUTER JOIN approach works great as you’ve shown. Thanks again. 👍

      Reply

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.