SQL:“不在"根据*不同*行的值选择行的替代方法? [英] SQL: "NOT IN" alternative for selecting rows based on values of *different* rows?

问题描述

How do you make an SQL statement that returns results modified by a subquery, or a join - or something else, that deals with information you're trying to return?

For example:

CREATE TABLE bowlers (
bowling_id int4 not null primary key auto_increment,
name text,
team text
);

Someone might incorrectly be on more than one team:

INSERT INTO `bowlers` (`name`, `team`) VALUES
('homer', 'pin pals'),
('moe', 'pin pals'),
('carl', 'pin pals'),
('lenny', 'pin pals'),
('homer', 'The homer team'),
('bart', 'The homer team'),
('maggie', 'The homer team'),
('lisa', 'The homer team'),
('marge', 'The homer team'),
('that weird french guy', 'The homer team');

So homer cannot decide on his team, so he's on both. Do'h!

I want to know everyone who is on, the homer team who is not also on the pin pals team. The best I can do is this:

SELECT a.name, a.team 
    FROM bowlers a where a.team = 'The homer team' 
    AND a.name 
    NOT IN (SELECT b.name FROM bowlers b WHERE b.team = 'pin pals');

Resulting in:

+-----------------------+----------------+
| name                  | team           |
+-----------------------+----------------+
| bart                  | The homer team | 
| maggie                | The homer team | 
| lisa                  | The homer team | 
| marge                 | The homer team | 
| that weird french guy | The homer team | 
+-----------------------+----------------+
5 rows in set (0.00 sec)

Which, you know, brilliant!

The performance will suffer, as the subquery is going to be run for each result of the query, which is B to the A to the D. Great for a few rows, Pretty bad for the hundreds of thousands of rows.

What is a better way? I am mostly thinking a self join would do the trick, but I can't wrap my head around how to do that.

Are there any other ways to do this, without using, NOT IN( SELECT ... )

Also, what is the name for this type of problem?

解决方案

Like this:

SELECT a.name, a.team
FROM bowlers a
LEFT OUTER JOIN bowlers b ON a.name = b.name AND b.team = 'pin pals'
WHERE a.team = 'The homer team'
AND b.name IS NULL;

You can also do it like this:

SELECT a.name, a.team
FROM bowlers a
WHERE a.team = 'The homer team'
AND NOT EXISTS (SELECT * FROM bowlers b
    WHERE b.team = 'pin pals'
    AND a.name = b.name
    );

By the way, this is called a "Left Anti-Semi Join".

这篇关于SQL:“不在"根据*不同*行的值选择行的替代方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！