Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
706 views
in Technique[技术] by (71.8m points)

mysql - SELECT that returns list of values not occurring in any row

Query:

select id from users where id in (1,2,3,4,5)

If the users table contains ids 1, 2, 3, this would return 1, 2, and 3. I want a query that would return 4 and 5. In other words, I don't want the query to return any rows that exist in the table, I want to give it a list of numbers and get the values from that list that don't appear in the table.

(updated to clarify question following several inapplicable answers)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

If you don't want to (explicitly) use temporary tables, this will work:

SELECT id FROM (
  (SELECT 1 AS id) UNION ALL
  (SELECT 2 AS id) UNION ALL
  (SELECT 3 AS id) UNION ALL
  (SELECT 4 AS id) UNION ALL
  (SELECT 5 AS id)
) AS list
LEFT JOIN users USING (id)
WHERE users.id IS NULL

However, it is quite ugly, quite long, and I am dubious about how it would perform if the list of IDs is long.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...