SQL запрос, как осуществить выборку данных одним махом?

Question

[symnoob]

Всем Привет,

пытаюсь отселектировать данные одним запросом, но не могу врубится где моя ошибка, к Примеру 2 Таблицы 1:н

Автолюбители и Автомобили, нужно выбрать всех Автолюбителей у которых нет ни одного Мерседеса или БМВ

Проблема моя именно в том что у Автолюбителя могут быть несколько Автомобилей: Мерседес, Ауди (вот этот уже должен отсеется).

А у другого Ауди и Волга (этот должен попасть в список)

Вот что я без успешно пробовал:

SELECT p.id FROM peoples p LEFT JOIN cars c on p.id = c.peoples_id WHERE c.desc NOT IN ('Mercedes', 'BMW');

Answer 1

[Rsa97]

SELECT `id` 
  FROM `peoples`
  WHERE `id` NOT IN (
    SELECT `peoples_id`
      FROM `cars`
      WHERE `desc` IN ('Mercedes', 'BMW')
  )

или

SELECT `p`.`id` 
  FROM `peoples` AS `p`
  LEFT JOIN `cars` AS `c`
    ON `c`.`peoples_id` = `p`.`id`
      AND `c`.`desc` IN ('Mercedes', 'BMW')
  WHERE `c`.`peoples_id` IS NULL

Comments

[Dark_Dante]

Второй вариант элегантнее

[Akina]

WHERE [NOT] IN - самый медленный из вариантов. LEFT JOIN [WHERE IS NULL] или WHERE [NOT] EXISTS предпочтительнее (оптимальность определяется средним количеством соответствующих записей).

[Rsa97]

Akina, Давайте посмотрим на EXPLAIN'ы запросов.

База

CREATE TABLE `peoples` (
  `id` INT PRIMARY KEY
);

CREATE TABLE `cars` (
  `id` INT PRIMARY KEY,
  `peoples_id` INT,
  `desc` VARCHAR(32),
  KEY `by_peoples_id` (`peoples_id`)
);

INSERT INTO `peoples` VALUES (1), (2), (3), (4);

INSERT INTO `cars` VALUES
(1, 1, 'Mercedes'), (2, 1, 'Volga'), (3, 2, 'Oka'),
(4, 3, 'Lada'), (5, 4, 'BMW'), (6, 4, 'Belaz');

Вариант с NOT IN:

| id  | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref | rows | filtered | Extra                    |
| --- | ----------- | ------- | ---------- | ----- | ------------- | ------- | ------- | --- | ---- | -------- | ------------------------ |
| 1   | PRIMARY     | peoples |            | index |               | PRIMARY | 4       |     | 4    | 100      | Using where; Using index |
| 2   | SUBQUERY    | cars    |            | ALL   | by_peoples_id |         |         |     | 6    | 33.33    | Using where              |

Вариант с LEFT JOIN/IS NULL:

| id  | select_type | table | partitions | type  | possible_keys | key     | key_len | ref | rows | filtered | Extra                                              |
| --- | ----------- | ----- | ---------- | ----- | ------------- | ------- | ------- | --- | ---- | -------- | -------------------------------------------------- |
| 1   | SIMPLE      | p     |            | index |               | PRIMARY | 4       |     | 4    | 100      | Using index                                        |
| 1   | SIMPLE      | c     |            | ALL   | by_peoples_id |         |         |     | 6    | 16.67    | Using where; Using join buffer (Block Nested Loop) |

Вариант с NOT EXISTS

| id  | select_type        | table | partitions | type  | possible_keys | key           | key_len | ref       | rows | filtered | Extra                    |
| --- | ------------------ | ----- | ---------- | ----- | ------------- | ------------- | ------- | --------- | ---- | -------- | ------------------------ |
| 1   | PRIMARY            | p     |            | index |               | PRIMARY       | 4       |           | 3    | 100      | Using where; Using index |
| 2   | DEPENDENT SUBQUERY | c     |            | ref   | by_peoples_id | by_peoples_id | 5       | test.p.id | 1    | 33.33    | Using where              |

В варианте с NOT EXISTS обнаруживается неприятная вещь - DEPENDENT SUBQUERY, то есть зависимый подзапрос, выполняющийся для каждой строки основного запроса. Потенциально, этот вариант будет самым медленным.

Варианты NOT IN и LEFT JOIN/IS NULL принципиально ничем не различаются.

[Akina]

Rsa97, 6 записей - несерьёзно. Только первичные ключи - тоже несерьёзно. Удалите первичные ключи и создайте вместо них простые индексы - самым быстрым будет вообще вариант с tablescan, не использующий индексы.

При рабочем наполнении и правильных индексах оптимальным будет LEFT JOIN, если на каждый peoples.id будет не более ~7-10 cars.peoples_id, иначе оптимальным станет WHERE NOT EXISTS.

Answer 2

[twobomb]

SELECT p.id FROM peoples as p WHERE NOT (SELECT COUNT(*) FROM cars as c WHERE c.peoples_id = p.id AND c.desc IN ('Mercedes', 'BMW'));

Answer 3

[Михаил]

SELECT p.* FROM peoples p --выбираем людей, для которых
WHERE NOT EXISTS ( --не существует
  SELECT 1 FROM cars c --машины
  WHERE c.peoples_id = p.id --принадлежаший этому человеку
  AND c.desc IN ('Mercedes', 'BMW') --и которая мерседес или бмв
)

Заменив NOT EXISTS на EXISTS можно получить обратный список - тех у кого есть мерс или бмв.
В случае поиска людей у которых НЕТ - полный аналог приведенного в ответе Rsa97 варианта с LEFT JOIN. Но в случае поиска людей у которых ЕСТЬ - вариант с LEFT JOIN может давать дублирующиеся строки, вариант с EXISTS всегда возвращает не более одной строки для каждого человека.