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.
NOT EXISTS can help in this case:
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);
SELECT * FROM Users AS U WHERE NOT EXISTS(SELECT 1 FROM Tags AS T WHERE T.user = U.id);
| ID | |----| | 2 |
(Thanks, @dotancohen for putting this into a SQLFiddle)