SQL:仅返回没有任何符合条件的关联的记录

问题描述：

如何仅返回没有任何符合特定条件的关联记录的记录?

How can I return only the records that do not have any associated records that meet a certain criteria?

例如，如果我与用户和地址之间存在一对多的关系，我如何获得所有在地址历史记录中没有特定城市的用户?

For example, if I have a one to many relationship between users and addresses, how can I get all the users who do NOT have a specific city in their address history?

SQL小提琴此处

示例数据在这里:

CREATE TABLE Users
  (`id` int, `name` varchar(20))
;

CREATE TABLE Addresses
  (`id` int, `city` varchar(20),`user_name` varchar(20))
;

INSERT INTO Users
  (`id`, `name`)

VALUES
  (1, 'sarah'),
  (2, 'harry'),
;

INSERT INTO Addresses
  (`id`, `city`, `user_name`)

VALUES
  (1, 'denver', 'sarah'),
  (2, 'anchorage', 'sarah'),
  (3, 'providence', 'harry'),
  (4, 'new york', 'harry')
;

答

最简单的方法是使用not exists或left join:

The simplest way is with not exists or left join:

select u.*
from users u left join
     addresses a
     on a.username = u.username and
        a.city = 'Peoria'
where a.city is null;

left join保留用户中的所有记录以及addresses中符合on条件的所有记录.在这种情况下(因为城市名称处于on条件)，它将返回所有具有城市信息或NULL值的用户. where子句选择NULL值-不匹配的值.

The left join keeps all records in users and any records in addresses that match the on conditions. In this case (because the city name is in the on condition), it returns all users with either information about the cities or NULL values. The where clause chooses the NULL values -- the non-matching ones.

等效的not exists可能更容易理解:

The equivalent not exists might be easier to follow:

select u.*
from users u 
where not exists (select 1
                  from addresses a
                  where a.username = u.username and
                        a.city = 'Peoria'
                 );

SQL:仅返回没有任何符合条件的关联的记录

相关推荐