SELECT, который возвращает список значений, не встречающихся ни в одной строке

запрос: select id from users where id in (1,2,3,4,5)

Если таблица users содержит идентификаторы 1, 2, 3, это вернет 1, 2 и 3. Я хочу, чтобы запрос возвращался 4 и 5. Другими словами, я не хочу, чтобы запрос возвращал любые строки, которые существуют в таблице, я хочу дать ему список чисел и получить значения из этого списка, которые не отображаются в таблице.

(обновлено, чтобы уточнить вопрос, следуя нескольким неприменимым ответам)

Ответ 1

Учитывая, что числа являются фиксированным списком. Самый быстрый способ, о котором я могу думать, - это иметь тестовую таблицу, заполненную этими числами и

непроверенный оператор выбора - но вы будете следовать за принцем.

select test.number from test left join users on test.number = users.id where test.number<>users.id

Затем вы получите все номера, которые не имеют соответствующего user.id, и поэтому могут заполнить отверстия.

Ответ 2

Если вы не хотите (явно) использовать временные таблицы, это будет работать:

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

Однако, это довольно уродливо, довольно долго, и я сомневаюсь в том, как он будет работать, если список идентификаторов длинный.

Ответ 3

Была такая же потребность и построена на ответе BugFinder, используя временную таблицу в сеансе. Таким образом, он будет автоматически уничтожен после выполнения запроса, поэтому мне не придется заниматься уборкой дома, поскольку я часто буду запускать этот тип запросов.

Создайте временную таблицу:

CREATE TEMPORARY TABLE tmp_table (id INT UNSIGNED);

Настройте tmp_table со значениями, которые вы проверите:

INSERT INTO tmp_table (id) values (1),(2),(3),(4),(5);

Когда таблица создана и заполнена, запустите запрос как с любой обычной таблицей:

SELECT tmp_table.id
  FROM tmp_table
  LEFT JOIN users u
  ON tmp_table.id = u.id
  WHERE u.id IS NULL;

Эта информация о временных таблицах MySQL также была полезной

Ответ 4

Другой вариант - использовать другую таблицу, содержащую все возможные идентификаторы, а затем сделать выбор:

mysql> describe ids;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id    | int(5) unsigned | NO   |     | 0       |       |
+-------+-----------------+------+-----+---------+-------+
1 row in set (0.05 sec)

mysql> select * from ids;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> select * from users;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)


mysql> select id from ids where id not in (select id from users);
+----+
| id |
+----+
|  4 |
|  5 |
+----+
2 rows in set (0.04 sec)

Добавленный побочный эффект - позволяет вам расширить список результатов, вставив в таблицу идентификаторов

Ответ 5

Напишите not in вместо in

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

Если вы хотите иметь противоположное в списке, но не в таблице. Temp_ids - это временная таблица, состоящая из вашего списка id

SELECT id FROM temp_ids WHERE temp_ids.id NOT IN (SELECT id FROM users)

Ответ 6

Извините, я не могу добавлять комментарии, @Austin,

У вас есть мой +1.

В любом случае, не уверен, что это работает на mysql, но измените все атомы, которые выбирают объединенные объединения для заданного значения, поэтому у вас есть что-то вроде этого:

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