从列表中选择不在表中的值 [英] Select values from a list that are not in a table

问题描述

我有一个ID列表:

(1, 2, 3, 6, 7)

和一张桌子:

id | anothercolumn
------------------
 1 | NULL
 2 | foo
 4 | bar
 5 | NULL
 6 | NULL

我想从列表中检索不是表ID的值.

I want to retrieve the values from my list which are not ids of my table.

预期结果:

3, 7

没有FROM的选择

我尝试过这样的事情:

SELECT without FROM

I tried something like this:

SELECT i
WHERE i IN (1, 2, 3, 6, 7)
AND i NOT IN (SELECT id FROM mytable);

但这不是有效的MySQL查询(必须为FROM).

But this is not a valid MySQL query (a FROM is required).

还有这种可能性:

SELECT i
FROM (
    SELECT 1 AS i
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 6
    UNION SELECT 7 ) AS mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;

这行得通，但是如果我的ID列表变大，查询很快就会很大...

This works, but if my list of ids becomes bigger, the query will soon be huge ...

我还可以为我的ID列表创建一个临时表:

I can also create a temporary table for my list of ids:

CREATE TEMPORARY TABLE mylistofids (
    i INT
);

INSERT INTO mylistofids (i) VALUES (1), (2), (3), (6), (7);

然后在LEFT JOIN中使用它:

SELECT i
FROM mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;

这也可行，但就我而言，我无权创建表(临时表或非临时表).

This works too, but in my case, I don't have the rights to create a table (temporary or not).

您看到一种以最好的方式解决此问题的方法吗?

Do you see a way to solve this problem in the nicest way possible?

推荐答案

临时表是最好的解决方案，但是如果不可能，则可以通过选择常量并将它们合并在一起来伪造临时表.

A temp table is the best solution, but if not possible then you can fudge a temp table by selecting constants and unioning them together.

使用此解决方案，您可以执行以下操作:-

Using this solution you can do the following:-

SELECT i
FROM 
(
    SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 6 UNION SELECT 7
) AS mylistofids 
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;

您还可以通过交叉连接数字范围来生成大量数字(假设您正在处理整数id).然后只需选择IN子句中的那些即可:-

You can also generate a massive range of numbers (assuming that you are dealing with integer ids) by cross joining ranges of numbers. Then just pick the ones that are in the IN clause:-

SELECT i
FROM 
(
    SELECT units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 + tensthousands.i * 10000 AS i
    FROM (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS units
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS tens
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS hundreds
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS thousands
    CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS tensthousands
) AS mylistofids 
LEFT JOIN mytable
ON mytable.id = i
WHERE mylistofids.i IN (1, 2, 3, 6, 7)
AND mytable.id IS NULL;

这篇关于从列表中选择不在表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！