SQL: "NOT IN" альтернатива для выбора строк на основе значений * разных * строк?

Как вы делаете инструкцию SQL, которая возвращает результаты, измененные подзапросом, или соединение - или что-то еще, которое касается информации, которую вы пытаетесь вернуть?

Например:

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

Кто-то может ошибочно быть в нескольких командах:

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');

Итак, homer не может принять решение о своей команде, поэтому он и на обоих. Do'h!

Я хочу знать всех, кто включен, the homer team, который также не находится в команде pin pals. Лучшее, что я могу сделать, это следующее:

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');

Результат:

+-----------------------+----------------+
| 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)

Который, вы знаете, блестящий!

Производительность будет страдать, поскольку подзапрос будет запущен для результата each запроса, который является B от A до D. Отлично подходит для нескольких строк, Довольно плохо для сотни тысяч строк.

Что лучше? Я в основном думаю, что самообслуживание будет делать трюк, но я не могу окунуться в голову, как это сделать.

Есть ли другие способы сделать это, не используя NOT IN( SELECT ... )

Также, каково имя для этого типа проблемы?

Ответ 1

Вот так:

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;

Вы также можете сделать это следующим образом:

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
    );

Кстати, это называется "Left Anti-Semi Join".

Ответ 2

Вы можете LEFT JOIN и убедиться, что объединенная таблица не имеет в ней данных (все равно null).

SELECT a.name, a.team 
    FROM bowlers a
    LEFT JOIN bowlers b
        ON b.name = a.name AND b.team = 'pin pals'
    WHERE a.team = 'The homer team' 
    AND a.name
    -- the join has to fail for this to be null
    AND b.bowling_id IS NULL