在这个场景中,我有两个表:members和team_members。成员表非常不言自明。团队成员表存储成员的团队信息(如果他们是团队成员)。如果团队成员表中没有member_id为用户的行,则他们不在团队中。我想做的是获得所有不是团队成员的用户。我应该使用左联接、内部联接、外部联接还是仅联接?这个查询会是什么样子?
CREATE TABLE IF NOT EXISTS `members` (
`member_id` int(15) NOT NULL AUTO_INCREMENT,
`group_id` int(15) NOT NULL,
`display_name` text NOT NULL,
`email_address` text NOT NULL,
`password` text NOT NULL,
`status` tinyint(1) NOT NULL,
`activation_code` varchar(16) NOT NULL,
`date_joined` text NOT NULL,
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `team_members` (
`team_member_id` int(15) NOT NULL AUTO_INCREMENT,
`member_id` int(15) NOT NULL,
`team_id` int(15) NOT NULL,
`date_joined` text NOT NULL,
`date_left` text NOT NULL,
`total_xp` int(15) NOT NULL,
PRIMARY KEY (`team_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
有几种方法可以编写此查询。
对我来说,这是最容易阅读和理解的:
select * from members where member_id not in (select member_id from team_members).
这是一个非常简单的写法。如果你决定想要所有的东西,你可以很快评论出where子句:
select m.* from members m left outer join team_members tm on m.member_id = tm.member_id
where tm.member_id is null
从我读到的SQL来看,这种方式似乎不太流行,但我认为它很简单:
select m.* from members m where not exists
(select member_id from team_members tm where tm.member_id = m.member_id)
从表面上看,下面的查询是好的
SELECT members.member_id
FROM members
LEFT OUTER JOIN team_members
ON team_members.member_id = members.member_id
WHERE team_members.member_id IS NULL
这是可以的,但在再次阅读你的问题时,你似乎有一个date_left列,如果你只想要那些尚未离开团队的成员,那么
SELECT members.member_id
FROM members
LEFT OUTER JOIN (SELECT *
FROM team_members
WHERE team_members.date_left != '') CURRENT_TEAMS
ON CURRENT_TEAMS.member_id = members.member_id
WHERE CURRENT_TEAMS.member_id IS NULL
SQLFiddle示例http://www.sqlfiddle.com/#!2/46b25/6/0