Предикат SQL IN для поиска значений, соответствующих указанному набору

Навигация по уроку

Предикат SQL IN и набор числовых значений
Предикат SQL IN и набор строковых значений
Предикат SQL IN и набор календарных значений
Предикат SQL IN и набор значений, возвращаемых подзапросом

Связанные темы

Предикат языка SQL IN в запросах на выборку данных применяется для извлечения из таблицы только тех строк, значения определённых столбцов в которых соответствуют набору значений, указываемых в скобках после IN.

Запрос с предикатом IN имеет следующий синстаксис:

SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ WHERE ИМЯ_СТОЛБЦА IN (ЗНАЧЕНИЕ N, ..., ЗНАЧЕНИЕ M)

Запрос с конструкцией NOT IN извлечёт строки, значения определённых столбцов в которых не сооветствуют набору указанных значений.

Предикат SQL IN и набор числовых значений

Если проверяемые значения столбцов имеют числовой тип данных, то в скобках после IN указывается набор чисел.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Работаем с базой данных "Недвижимость". Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке.

Таблица Object содержит данные об объектах. В этой таблице нам для первых двух примеров понадобятся значения столбцов Rooms (число комнат) и District (район). Для третьего примера понадобится таблица Deal (сделка) и значения столца Date (дата заключения сделки).

Пример 1. Из таблицы Object требуется выбрать объекты с числом комнат 2 и 3. Пишем запрос:

SELECT Obj_ID, Type, District, Rooms FROM Object WHERE Rooms IN (2, 3)

Результат выполнения запроса:

Obj_ID	Type	District	Rooms
1	flat	Центр	2
2	flat	Центр	2
4	flat	Центр	2
6	flat	Пашино	2
7	flat	Центр	3
8	house	Сосновка	3
10	flat	Пашино	2
11	flat	Сосновка	3
12	flat	Сосновка	2

Запрос с предикатом IN аналогичен следующему запросу:

SELECT Obj_ID, Type, District, Rooms FROM Object WHERE Rooms=2 OR Rooms=3

Но как видим запрос с предикатом IN намного короче, что особенно важно, если в наборе более двух значений.

Предикат SQL IN и набор строковых значений

Аналогично работает запрос с предикатом IN в случаях набора строковых значений.

Пример 2. Из таблицы Object требуется выбрать объекты, расположенные в районах Сосновка и Пашино. Пишем запрос:

SELECT Obj_ID, Type, District, Rooms FROM Object WHERE District IN ('Сосновка', 'Пашино')

Результат выполнения запроса:

Obj_ID	Type	District	Rooms
6	flat	Пашино	2
8	house	Сосновка	3
9	flat	Сосновка	1
10	flat	Пашино	2
11	flat	Сосновка	3
12	flat	Сосновка	2

Предикат SQL IN и набор календарных значений

Запрос с предикатом IN может быть применён и для отбора строк, значения столбцов в которых соответствуют определённым значениям даты и времени.

Пример 3. Из таблицы Deal требуется выбрать сделки, заключённые в августе и сентябре. Пишем запрос:

SELECT Deal_ID, Type, Cost, Date FROM Deal WHERE MONTH(Date) IN (08, 09)

Результат выполнения запроса:

Deal_ID	Type	Cost	Date
4	sale	120000	2018-08-15
5	sale	45000	2018-08-17
6	sale	48000	2018-09-09
7	sale	90000	2018-09-16
8	rent	15000	2018-09-30

Примеры запросов к базе данных "Недвижимость" есть также в уроках по оператору GROUP BY, предикату EXISTS, функциям ALL и ANY и LIMIT.

Предикат SQL IN и набор значений, возвращаемых подзапросом

Часто требуется выбрать строки, значения столбцов в которых соответствуют большому набору данных и этот набор не всегда известен специалисту по работе с базой данных. Тогда составляются сложные запросы, в которых в скобках после предиката IN пишется подзапрос (вложенный запрос). По набору значений, возвращаемому этим подзапросом и происходит отбор строк в основном запросе. Иногда такие сложные запросы могут иметь несколько уровней вложения. Синтаксис запроса с подзапросами следующий:

SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ WHERE ИМЯ_СТОЛБЦА IN (SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ [WHERE УСЛОВИЕ])

Далее будем работать с базой данных библиотеки. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке.

Пример 4. Итак, есть база данных библиотеки и её таблица "Книга в пользовании" (Bookinuse). Столбец Customer_ID содержит идентификационный номер пользователя.

Author	Title	Pubyear	Inv_No	Customer_ID
Толстой	Война и мир	2005	28	65
Чехов	Вишневый сад	2000	17	31
Чехов	Избранные рассказы	2011	19	120
Чехов	Вишневый сад	1991	5	65
Ильф и Петров	Двенадцать стульев	1985	3	31
Маяковский	Поэмы	1983	2	120
Пастернак	Доктор Живаго	2006	69	120
Толстой	Воскресенье	2006	77	47
Толстой	Анна Каренина	1989	7	205
Пушкин	Капитанская дочка	2004	25	47
Гоголь	Пьесы	2007	81	47
Чехов	Избранные рассказы	1987	4	205
Пушкин	Сочинения, т.1	1984	6	47
Пастернак	Избранное	2000	137	18
Пушкин	Сочинения, т.2	1984	8	205
NULL	Наука и жизнь 9 2018	2019	127	18
Чехов	Ранние рассказы	2001	171	31

Определить ID пользователей, которым выданы книги авторов, книги которых выданы пользователю с ID 31. Запрос будет следующим:

SELECT DISTINCT Customer_ID FROM Bookinuse WHERE Author IN (SELECT Author FROM Bookinuse WHERE Customer_ID=31)

Результатом выполнения запроса будет следующая таблица:

Customer_ID

120

205

Внутренний запрос (после IN) выбирает авторов: Чехов; Ильф и Петров. Внешний запрос выбирает всех пользователей, которым выданы книги этих авторов. Видим, что, в отличие от предиката EXISTS, предикат IN предваряется именем столбца, в данном случае - Author.

Примеры запросов к базе данных "Библиотека" есть также в уроках по оператору GROUP BY, предикату EXISTS и функциям CONCAT, COALESCE.

В следующих примерах работаем с базой данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 5. Вывести актёрский состав спектакля "War of the Roses".

Пишем следующий запрос с двумя подзапросами:

SELECT FName, LName FROM ACTOR WHERE Actor_ID IN (SELECT Actor_ID FROM Team WHERE Play_ID=(SELECT Play_ID FROM Play WHERE Name='War of the Roses'))

Второй по уровню вложенности запрос возвращает идентификаторы актёров. Они и принимаются предикатом IN и по этому набору происходит отбор строк во внешнем запросе.

Написать запросы с предикатом IN самостоятельно, а затем посмотреть решения

Пример 6. Вывести список актёров, которые не разу не были утверждены на главную роль. В таблице team данные о главных ролях содержатся в столбце mainteam. Если роль - главная, то в соответствующей строке отмечено 'Y'.

Правильное решение и ответ.

Пример 7. Вывести список актеров, которые играли во всех спектаклях WilliamShakespeare. Данные об авторах содержается в таблице play в столбце author.

Правильное решение и ответ.

Пишем вместе ещё один запрос с предикатом IN

Пример 8. Вывести список актёров, которые не задействованы в постановках последних 3 лет.

Пишем следующий запрос с двумя подзапросами:

SELECT FName, LName FROM ACTOR WHERE Actor_ID NOT IN (SELECT Actor_ID FROM Team WHERE Play_ID IN (SELECT Play_ID FROM Play WHERE Premieredate > (CURDATE() - 3 YEAR)))

Предикат IN используется два раза. Первый раз (причём с отрицанием NOT) - со вторым по уровню вложенности запросом, который возвращает идентификаторы актёров. Второй раз - с третьим по уровню вложенности запросом, котрый возвращает идентификаторы постановок.

Поделиться с друзьями

Назад<<<

Содержание

Вперёд>>>