SQL 如何查找未连接的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/151099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find records that are not joined?
提问by Sixty4Bit
I have two tables that are joined together.
我有两个连接在一起的表。
A has many B
A有很多B
Normally you would do:
通常你会这样做：
select * from a,b where b.a_id = a.id
To get all of the records from a that has a record in b.
从 a 中获取在 b 中有记录的所有记录。
How do I get just the records in a that does not have anything in b?
如何只获取 a 中没有 b 中任何内容的记录？
回答by albertein
select * from a where id not in (select a_id from b)
Or like some other people on this thread says:
或者像这个线程上的其他一些人说：
select a.* from a
left outer join b on a.id = b.a_id
where b.a_id is null
回答by Joseph Anderson
select * from a
left outer join b on a.id = b.a_id
where b.a_id is null
回答by Matt Hamilton
Another approach:
另一种方法：
select * from a where not exists (select * from b where b.a_id = a.id)
The "exists" approach is useful if there is some other "where" clause you need to attach to the inner query.
如果您需要将其他一些“where”子句附加到内部查询，则“exists”方法很有用。
回答by onedaywhen
SELECT id FROM a
EXCEPT
SELECT a_id FROM b;
回答by nathan
You will probably get a lot better performance (than using 'not in') if you use an outer join:
如果您使用外连接，您可能会获得更好的性能（比使用 'not in'）：
select * from a left outer join b on a.id = b.a_id where b.a_id is null;
回答by BlackWasp
SELECT <columnns>
FROM a WHERE id NOT IN (SELECT a_id FROM b)
回答by Monsif EL AISSOUSSI
The following image will help to understand SQL LET JOIN:
下图将有助于理解 SQL LET JOIN：
回答by Petr ?tipek
In case of one join it is pretty fast, but when we are removing records from database which has about 50 milions records and 4 and more joins due to foreign keys, it takes a few minutes to do it. Much faster to use WHERE NOT IN condition like this:
在一个连接的情况下它非常快，但是当我们从数据库中删除记录时，由于外键有大约 5000 万条记录和 4 个或更多连接，需要几分钟才能完成。像这样使用 WHERE NOT IN 条件要快得多：
select a.* from a
where a.id NOT IN(SELECT DISTINCT a_id FROM b where a_id IS NOT NULL)
//And for more joins
AND a.id NOT IN(SELECT DISTINCT a_id FROM c where a_id IS NOT NULL)
I can also recommended this approach for deleting in case we don't have configured cascade delete. This query takes only a few seconds.
如果我们没有配置级联删除，我也可以推荐这种删除方法。这个查询只需要几秒钟。
回答by Daniele Licitra
The first approach is
第一种方法是
select a.* from a where a.id not in (select b.ida from b)
the second approach is
第二种方法是
select a.*
from a left outer join b on a.id = b.ida
where b.ida is null
The first approach is very expensive. The second approach is better.
第一种方法非常昂贵。第二种方法更好。
With PostgreSql 9.4, I did the "explain query" function and the first query as a cost of cost=0.00..1982043603.32. Instead the join query as a cost of cost=45946.77..45946.78
使用 PostgreSql 9.4，我执行了“解释查询”功能，并将第一个查询作为cost=0.00..1982043603.32的成本。而是将连接查询作为成本=45946.77..45946.78
For example, I search for all products that are not compatible with no vehicles. I've 100k products and more than 1m compatibilities.
例如，我搜索所有不兼容任何车辆的产品。我有 10 万种产品和超过 100 万种兼容性。
select count(*) from product a left outer join compatible c on a.id=c.idprod where c.idprod is null
The join query spent about 5 seconds, instead the subquery version has never ended after 3 minutes.
连接查询花费了大约 5 秒，而子查询版本在 3 分钟后从未结束。
回答by shahkalpesh
Another way of writing it
另一种写法
select a.*
from a
left outer join b
on a.id = b.id
where b.id is null
Ouch, beaten by Nathan :)
哎哟，被内森打败了:)
