Базы данных
и язык SQL

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

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

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

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

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

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

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

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

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

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

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

Obj_IDTypeDistrictRooms
1flatЦентр2
2flatЦентр2
4flatЦентр2
6flatПашино2
7flatЦентр3
8houseСосновка3
10flatПашино2
11flatСосновка3
12flatСосновка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_IDTypeDistrictRooms
6flatПашино2
8houseСосновка3
9flatСосновка1
10flatПашино2
11flatСосновка3
12flatСосновка2

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

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

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

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

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

Deal_IDTypeCostDate
4sale1200002018-08-15
5sale450002018-08-17
6sale480002018-09-09
7sale900002018-09-16
8rent150002018-09-30

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

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

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

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

AuthorTitlePubyearInv_NoUser_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
ТолстойВоскресенье20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПастернакИзбранное200013718

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

SELECT User_ID FROM BOOKINUSE WHERE Author IN (SELECT Author FROM BOOKINUSE WHERE User_ID=31)

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

User_ID
120
65
205

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

В следующих примерах работаем с базой данных "Театр". Таблица 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) - со вторым по уровню вложенности запросом, который возвращает идентификаторы актёров. Второй раз - с третьим по уровню вложенности запросом, котрый возвращает идентификаторы постановок.

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

Реляционные базы данных и язык SQL