SQL 选择连接帮助

2021-09-10 00:00:00 tsql sql-server

我在找出针对此场景的查询时遇到问题.我想在这个查询中使用两个表，它们是这样的:

I'm having problems figuring out a query for this scenario. I have two tables I want to use in this query, they are like this:

Units
  ID
  Other Data
People
  ID
  UnitID  <-- fk to Units
  Other Data

这就是我想做的:
我想选择在 People 表中没有与它们链接的行的所有单位.我该怎么做?

This is what I want to do:
I want to select all the units that do NOT have a row in the People table linked to them. How can I do this?

推荐答案

SELECT Units.* FROM Units LEFT JOIN People ON People.UnitID = Units.ID WHERE People.ID IS NULL

或者替代

SELECT Units.* FROM Units WHERE NOT EXISTS (SELECT 1 FROM People WHERE People.UnitID = Units.ID)

甚至

SELECT Units.* FROM Units WHERE Units.ID NOT IN (SELECT UnitID FROM People)

相关文章