select from database table rows that are not in another table

Question

I have a table of 'entries' in a MYSQL database. I have another table that records activity on those entries, with the id of the entry as a foreign key. I want to select from my first table entries that do not appear in the second table.

How can I use SQL to make this happen? Do I have to iterate through both tables and compare every entry with every other entry? Is there an easier way to do this?

ex. I have a table with an entry data column and a user name column. I have another table with an entry id column and a user id column. I want to select from my first table all of the entries which do not appear in the second table with a given user id.

Thanks ahead of time. I have been struggling with this experiment for a while. I imagine I have to join the two tables somehow?

sgeddes · Accepted Answer · 2014-09-18 18:41:42Z

3

Several ways to achieve this, NOT IN, NOT EXISTS, LEFT JOIN / NULL check. Here's one with NOT EXISTS:

SELECT * FROM FirstTable T WHERE NOT EXISTS ( SELECT * FROM SecondTable T2 WHERE T.Id = T2.Id )

answered Sep 18, 2014 at 18:41

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shane Over a year ago

Gervs can you clarify?

sgeddes Over a year ago

@user3667450 -- there's an age old debate that when using EXISTS, you can use SELECT * or SELECT 1. Most modern DBMS's will actually produce the same execution plan for these queries. I prefer the readability of SELECT *, but they both work just as well. So if you would prefer, you can write ...WHERE NOT EXISTS (SELECT 1....

Neghtasro · Accepted Answer · 2014-09-18 18:46:48Z

From what I understand, you want to select all rows where the foreign key doesn't match anything in the other table. This should do the trick:

SELECT * FROM Data A RIGHT JOIN Entry B ON A.ID = B.ID WHERE A.ID IS NULL

Here's a handy chart that illustrates how to use joins for stuff like this.

You can also use NOT IN, and the mechanics for this one are actually a bit easier to understand.

SELECT * FROM Data A WHERE A.ID NOT IN (SELECT ID FROM Entry)

Collectives™ on Stack Overflow

select from database table rows that are not in another table

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related