Get unmatched rows for foreign key

Posted on

Question :

Consider two tables:

CREATE TABLE Users(
  id INT PRIMARY KEY,
  name VARCHAR(128)
) Engine=InnoDB

CREATE TABLE Tags(
  id INT PRIMARY KEY,
  user REFERENCES Users(id),
  tag VARCHAR(128)
) Engine=InnoDB

I would like to know which Users do not have any Tags assigned to them. I could do a LEFT JOIN and then add a WHERE clause for no Tags, but as there are tens of thousands of Users and each could have a few tens of Tags, I think that would be impractical. Is there a better way?

This is running on MySQL 5.5 and 5.6 on Ubuntu Server 12.04 running in Amazon EC2.

Answer :

A NOT EXISTS can help in this case:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Users(Id INT PRIMARY KEY);
CREATE TABLE Tags(Id INT PRIMARY KEY,User INT REFERENCES User(Id));


INSERT INTO Users(Id) VALUES(1);
INSERT INTO Users(Id) VALUES(2);


INSERT INTO Tags(Id,User)VALUES(1,1);

Query 1:

SELECT * FROM Users AS U WHERE NOT EXISTS(SELECT 1 FROM Tags AS T WHERE T.user = U.id);

Results:

| ID |
|----|
|  2 |

(Thanks, @dotancohen for putting this into a SQLFiddle)

Leave a Reply

Your email address will not be published. Required fields are marked *