Как использовать несколько таблиц в качестве значения для одного NOT IN condition?

ProgramBox

Как использовать несколько таблиц в качестве значения для одного NOT IN condition?

Post author:admin
Запись опубликована:28 июня, 2022
Post category:Вопросы по программированию

#mysql

Вопрос:

У меня есть таблица категорий, продуктов и клиентов в моей базе данных. Каждая из этих таблиц имеет столбец с именем image_id, который будет ссылаться на таблицу изображений как на внешний ключ. В принципе, мне нужно получить записи таблицы изображений, где image_id отсутствует в таблице category, product и customer.

Вот мой текущий запрос:

 SELECT
    *
FROM
    `image`
WHERE
    image.id NOT IN(
    SELECT
        product.image_id
    FROM
        product AS product
    WHERE
        product.image_id IS NOT NULL
) AND image.id NOT IN(
    SELECT
        category.image_id
    FROM
        category AS category
    WHERE
        category.image_id IS NOT NULL
) AND image.id NOT IN(
    SELECT
        customer.image_id
    FROM
        customer AS customer
    WHERE
        customer.image_id IS NOT NULL
)

Запрос работает нормально, но условие NOT IN выглядит повторяемым, есть ли способ, с помощью которого я могу преобразовать эти множественные условия NOT IN в одно единственное условие NOT IN?

Ответ №1:

используйте LEFT JOIN, затем добавьте предложение WHERE, чтобы отфильтровать ненулевое значение

 SELECT i.* FROM
  `image` i
  LEFT JOIN `product`  p ON p.image_id=i.id
  LEFT JOIN `category` c ON c.image_id=i.id
  LEFT JOIN `customer` u ON u.image_id=i.id
WHERE
  p.image_id IS NULL
  AND c.image_id IS NULL
  AND u.image_id IS NULL

Это работает, потому что LEFT JOIN — это СОЕДИНЕНИЕ, которое всегда выбирает левую таблицу (в данном случае image таблицу), тогда, если правая таблица (в данном случае product , category , и customer таблица) не имеет объединяемой записи, ее значение будет равно null .

Указав WHERE p.image_id IS NULL , вы попросите выбрать изображения, у которых нет объединяемой записи в product таблице. Повторите тот же шаг для category and customer .

Еще одним преимуществом является то, что этот запрос выполняется быстрее, чем ваш, поскольку у вас есть 3 подзапроса, вы можете проверить это с помощью инструкции explain query .

Сравнение запроса EXPLAIN:

 CREATE TABLE `test`.`image` ( `id` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `test`.`product` ( `id` INT NOT NULL , `image_id` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `test`.`category` ( `id` INT NOT NULL , `image_id` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
CREATE TABLE `test`.`customer` ( `id` INT NOT NULL , `image_id` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
ALTER TABLE `product` ADD INDEX (`image_id`);
ALTER TABLE `category` ADD INDEX (`image_id`);
ALTER TABLE `customer` ADD INDEX (`image_id`);

объясните свой NOT IN подзапрос

 EXPLAIN SELECT
    *
FROM
    `image`
WHERE
    image.id NOT IN(
    SELECT
        product.image_id
    FROM
        product AS product
    WHERE
        product.image_id IS NOT NULL
) AND image.id NOT IN(
    SELECT
        category.image_id
    FROM
        category AS category
    WHERE
        category.image_id IS NOT NULL
) AND image.id NOT IN(
    SELECT
        customer.image_id
    FROM
        customer AS customer
    WHERE
        customer.image_id IS NOT NULL
);

Результат:

  ------ -------------------- ---------- ---------------- --------------- ---------- --------- ------ ------ -------------------------- 
| id   | select_type        | table    | type           | possible_keys | key      | key_len | ref  | rows | Extra                    |
 ------ -------------------- ---------- ---------------- --------------- ---------- --------- ------ ------ -------------------------- 
|    1 | PRIMARY            | image    | index          | NULL          | PRIMARY  | 4       | NULL | 1    | Using where; Using index |
|    4 | DEPENDENT SUBQUERY | customer | index_subquery | image_id      | image_id | 4       | func | 1    | Using index; Using where |
|    3 | DEPENDENT SUBQUERY | category | index_subquery | image_id      | image_id | 4       | func | 1    | Using index; Using where |
|    2 | DEPENDENT SUBQUERY | product  | index_subquery | image_id      | image_id | 4       | func | 1    | Using index; Using where |
 ------ -------------------- ---------- ---------------- --------------- ---------- --------- ------ ------ --------------------------

объясните мое ЛЕВОЕ СОЕДИНЕНИЕ ГДЕ NULL

 EXPLAIN SELECT i.* FROM
  `image` i
  LEFT JOIN `product`  p ON p.image_id=i.id
  LEFT JOIN `category` c ON c.image_id=i.id
  LEFT JOIN `customer` u ON u.image_id=i.id
WHERE
  p.image_id IS NULL
  AND c.image_id IS NULL
  AND u.image_id IS NULL;

Результат:

  ------ ------------- ------- ------- --------------- ---------- --------- ----------- ------ -------------------------------------- 
| id   | select_type | table | type  | possible_keys | key      | key_len | ref       | rows | Extra                                |
 ------ ------------- ------- ------- --------------- ---------- --------- ----------- ------ -------------------------------------- 
|    1 | SIMPLE      | i     | index | NULL          | PRIMARY  | 4       | NULL      | 1    | Using index                          |
|    1 | SIMPLE      | p     | ref   | image_id      | image_id | 4       | test.i.id | 1    | Using where; Using index; Not exists |
|    1 | SIMPLE      | c     | ref   | image_id      | image_id | 4       | test.i.id | 1    | Using where; Using index; Not exists |
|    1 | SIMPLE      | u     | ref   | image_id      | image_id | 4       | test.i.id | 1    | Using where; Using index; Not exists |
 ------ ------------- ------- ------- --------------- ---------- --------- ----------- ------ --------------------------------------

1. итак, я должен поместить этот оператор выбора левого соединения внутри NOT IN condition?

2. нет, вы должны переписать весь свой запрос, чтобы использовать соединение по левому краю вместо not in подзапрос

Ответ №2:

Вы можете просто выполнить UNION в своем подзапросе следующее:

 SELECT *
FROM image i
  WHERE id NOT IN
    (SELECT image_id FROM product UNION
      SELECT image_id FROM category UNION
      SELECT image_id FROM customer);

* Обратите внимание, что это UNION дает вам отличные результаты (исключая повторяющиеся значения), в то время UNION ALL как возвращает все значения, включая дубликаты. Оба могут быть применены здесь, но, насколько я понимаю, UNION ALL это может быть быстрее, потому что у него не было дополнительного процесса удаления дубликатов. Однако, если вы работаете с небольшим набором данных и у вас есть правильная индексация, это не должно сильно влиять на производительность.

Тот же UNION подзапрос также может эмулировать LEFT JOIN предложенный @Kristian, но с другой структурой запроса; при этом вам понадобится только одно WHERE условие:

 SELECT i.*
FROM image i
LEFT JOIN
 (SELECT image_id FROM product UNION
    SELECT image_id FROM category UNION
     SELECT image_id FROM customer) B ON i.id=B.image_id
WHERE B.image_id IS NULL;

Демонстрационная скрипка

Кстати, присвоение таблице псевдонима, похожего на ее имя, является просто избыточностью, и вам также не нужно добавлять имя таблицы в подзапрос, если это только одна таблица, ну, в большинстве случаев, если вы не используете функцию like NOT EXISTS , и таблицы имеют похожие имена столбцов. Посмотрите на этот пример из вашего запроса:

 ...
WHERE
    image.id NOT IN(
    SELECT
        product.image_id
    FROM
        product AS product  <---- this is not necessary
    WHERE
        product.image_id IS NOT NULL)
        ^^^^^^^
        this also no need
...

Вместо этого вы можете просто написать их следующим образом:

 WHERE
    image.id NOT IN(
    SELECT
        image_id
    FROM
        product
    WHERE
        image_id IS NOT NULL)

Метки: Как использовать несколько таблиц в качестве значения для одного НЕ В состоянии?