запрос: select id from users where id in (1,2,3,4,5)
Если таблица users содержит идентификаторы 1, 2, 3, это вернет 1, 2 и 3. Я хочу, чтобы запрос возвращался 4 и 5. Другими словами, я не хочу, чтобы запрос возвращал любые строки, которые существуют в таблице, я хочу дать ему список чисел и получить значения из этого списка, которые не отображаются в таблице.
(обновлено, чтобы уточнить вопрос, следуя нескольким неприменимым ответам)
Учитывая, что числа являются фиксированным списком. Самый быстрый способ, о котором я могу думать, - это иметь тестовую таблицу, заполненную этими числами и
непроверенный оператор выбора - но вы будете следовать за принцем.
select test.number from test left join users on test.number = users.id where test.number<>users.id
Затем вы получите все номера, которые не имеют соответствующего user.id, и поэтому могут заполнить отверстия.
Если вы не хотите (явно) использовать временные таблицы, это будет работать:
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
Однако, это довольно уродливо, довольно долго, и я сомневаюсь в том, как он будет работать, если список идентификаторов длинный.
Была такая же потребность и построена на ответе 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 также была полезной
Другой вариант - использовать другую таблицу, содержащую все возможные идентификаторы, а затем сделать выбор:
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)
Добавленный побочный эффект - позволяет вам расширить список результатов, вставив в таблицу идентификаторов
Напишите 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)
Извините, я не могу добавлять комментарии, @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