Операторы IN и NOT IN в SQL запросах
Использование операторов IN и NOT IN позволяет фильтровать значения из одного столбца таблицы по списку значений. В ином случае для выполнения фильтрации нам было бы необходимо сравнивать значения столбца с каждым значением из списка по отдельности, что ухудшило бы его читаемость и значительно увеличило объем запроса. Так же стоит отметить что производительность запросов с оператором IN значительно выше чем при использовании нескольких операторов OR. Эти операторы так же удобны для использования вместе с подзапросами, что позволит выполнить запрос, когда у вас список с тысячами, или миллионами значений.
Синтаксис запроса с использованием операторов IN и NOT IN следующий:
Синтаксис запроса с использованием операторов IN и NOT IN следующий:
SELECT column1, column2, ...
FROM table
WHERE column2 IN ('value1', 'value2', 'value3', '...')
А теперь давайте рассмотрим использование этих операторов на примере таблиц с покупателями и их покупками:
FROM table
WHERE column2 IN ('value1', 'value2', 'value3', '...')
CREATE TABLE buyers
(
id_buyer INT PRIMARY KEY AUTO_INCREMENT,
firstname VARCHAR(15),
lastname VARCHAR(15)
);
CREATE TABLE sales
(
id_product INT PRIMARY KEY AUTO_INCREMENT,
id_buyer INT(4),
product VARCHAR(50)
);
INSERT INTO buyers(firstname, lastname) VALUES
('Carlos', 'Boolman'),
('Logan', 'Evans'),
('Laura', 'White'),
('Ethan', 'Walker'),
('Mary', 'Wilson');
INSERT INTO sales(id_buyer, product) VALUES
(5, 'Bread'), (2, 'Eggs'), (3, 'Meat'),
(5, 'Milk'), (4, 'Juice'), (1, 'Tea'),
(4, 'Lemon'), (1, 'Bread'), (4, 'Sugar');
Допустим, нам необходимо получить все покупки, которые были совершены только определенными покупателями. Мы могли бы прописать несколько условий is_buyer=... через оператор OR, но гораздо удобнее указать список идентификаторов покупателей в операторе IN. Таким же образом можно получить список покупок всех покупателей, которые не состоят в списке. Для этого воспользуемся оператором NOT IN.
(
id_buyer INT PRIMARY KEY AUTO_INCREMENT,
firstname VARCHAR(15),
lastname VARCHAR(15)
);
CREATE TABLE sales
(
id_product INT PRIMARY KEY AUTO_INCREMENT,
id_buyer INT(4),
product VARCHAR(50)
);
INSERT INTO buyers(firstname, lastname) VALUES
('Carlos', 'Boolman'),
('Logan', 'Evans'),
('Laura', 'White'),
('Ethan', 'Walker'),
('Mary', 'Wilson');
INSERT INTO sales(id_buyer, product) VALUES
(5, 'Bread'), (2, 'Eggs'), (3, 'Meat'),
(5, 'Milk'), (4, 'Juice'), (1, 'Tea'),
(4, 'Lemon'), (1, 'Bread'), (4, 'Sugar');
SELECT id_buyer, product FROM sales WHERE id_buyer IN (1, 3, 4)
SELECT id_buyer, product FROM sales WHERE id_buyer NOT IN (1, 3, 4)
Как мы писали ранее в теле рассматриваемых операторов можно использовать не только списки значений, но и подзапросы. К примеру у нас нет списка идентификаторов покупателей, а есть только имена, но из-за того что в таблице покупок есть только значения идентификаторов, нам для начала необходимо их получить с помощью подзапроса.
SELECT id_buyer, product FROM sales WHERE id_buyer NOT IN (1, 3, 4)
SELECT id_buyer, product FROM sales WHERE id_buyer IN (
SELECT id_buyer FROM buyers WHERE firstname='Carlos' OR firstname='Mary'
);
Помимо использования данных операторов в запросах вида SELECT, данные операторы отлично подходят для фильтрации данных при использовании в запросах INSERT, DELETE и UPDATE.
SELECT id_buyer FROM buyers WHERE firstname='Carlos' OR firstname='Mary'
);