Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
4  
by the way: inline foreign keys - like user REFERENCES Users(id) - are ignored in MySQL. Define them at the end of table definition - with FOREIGN KEY (user) REFERENCES Users(id). –  ypercube Oct 9 '13 at 12:37
1  
@ypercube +1 ad. infinitum –  swasheck Oct 9 '13 at 12:57
    
@ypercube: Thank you, I had no idea! –  dotancohen Oct 9 '13 at 13:59
    
@ypercube, just one of those many reasons why you can't rely on MySQL. I created a SQL Fiddle to show that this is indeed broken: sqlfiddle.com/#!2/51fc8/1 –  Sebastian Meine Oct 9 '13 at 14:14
    
@SebastianMeine it probably throws a warning. –  ypercube Oct 9 '13 at 14:31

1 Answer 1

up vote 4 down vote accepted

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)

share|improve this answer
    
Thank you. I love the simplicity of the answer. –  dotancohen Oct 9 '13 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.