Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

В рассматриваемом нами варианте в этом шаге вместо списка значений мы сможем задать подзапрос, возвращающий необходимые нам значения.

Синтаксис

Примеры

Вы помните, по каким колонкам связаны таблицы в нашей схеме? Если нет, то посмотрите, пожалуйста, на схему ниже.

AUTO — > MAN = PHONENUM

CITY — > MAN = CITYCODE

Это может понадобиться при рассмотрении примеров.

Выбрать все машины * AUTO, где владельцам (MAN) больше 35 лет (YEAROLD). Использовать IN с подзапросом.

Рисунок 109. Пример запроса с IN SELECT

Выбрать все города (CITY), где есть записи из таблицы MAN. Использовать IN с подзапросом.

Рисунок 110. Пример запроса к CITY: запрос к MAN

Выбрать все записи из MAN, где люди проживают в городах (CITY) с населением (PEOPLES) больше 700 000 человек. Использовать IN с подзапросом.

Рисунок 111. Запрос с IN SELECT к таблице MAN

Тип данных сравниваемого списка SELECT должен совпадать с типом данных колонки сравнения, иначе необходимо прибегнуть к преобразованию типов.

При конструкции IN c подзапросом игнорируются NULL-значения. Для работы с NULL-значениями необходимо использовать функцию преобразования NVL.

Каким образом написать IN по нескольким колонкам, что-то подобное следующему запросу?

Нет, такой синтаксис классический SQL не поддерживает, так сделать не получится. Сравнение в IN осуществляется по одной колонке, но всегда можно использовать другие варианты. Hапример, конструкцию EXISTS.

1. Выбрать все машины (AUTO), где имя владельца MAN (fIStNAME) начинается с буквы А, использовать IN с подзапросом.

2. Выбрать все города (CITY), где есть записи из таблицы MAN и людям больше 35 лет (YEAROLD), использовать IN с подзапросом.

3. Выбрать все машины (AUTO), где возраст владельца больше 37 лет (YEAROLD) и длина имени больше пяти букв (FIRSTNAME), использовать IN с подзапросом.

Как правило, запросы SQL с конструкцией EXISTS воспринимаются учениками как наиболее сложный материал.

Итак, подзапрос с использованием EXISTS является наиболее сложным для понимания типом подзапроса в SQL.

Но мы попробуем разобраться, и здесь очень важно осознать, что подзапрос EXIST является предикатом, возвращает нам либо истину, либо ложь, то есть подзапрос с EXIST — это критерий того, будет на экран выведена данная строка либо нет. Если подзапрос возвращает хоть одну строку, то внешний запрос выводит данные — конкретную связанную строчку, если нет, данные не выводятся.

Также подзапрос EXISTS подразумевает объединение, то есть внутренний подзапрос связывается определенным отношением с внешним запросом.

Рассмотрим синтаксис EXISTS

Здесь (SELECT 1 FROM таблица2 т2 WHERE t1.KEY = t2.KEY) является внутренним подзапросом, который либо возвращает строку, либо нет, отношением, влияющим на поведение внешнего запроса.

Если (SELECT 1 FROM таблица2 т2 WHERE t1.KEY = t2.KEY) возвращает какие-либо данные для внешнего запроса, тогда SELECT поля FROM таблица1 т1выводит соответствующую строку на экран.

Впрочем, гораздо проще всего понять работу EXIST на примерах.

Итак, примеры:

Вывести на экран все города * из таблицы CITY, для которых есть соответствующая запись таблицы MAN, использовать EXISTS, связь по полю CITYCODE.

Рисунок 112. Запрос c EXISTS к таблице MAN

Подзапрос EXISTS выбирает данные, связанные с внешним запросом по колонке CITYCODE, то есть для каждой возвращаемой строки внешнего запроса SELECT * FROM CITY проверяется по CITYCODE наличие такой строки во внутреннем запросе SELECT 1 FROM MAN m WHERE c.CITYCODE = m.CITYCODE.

Если подзапрос находит такую строку, то строчка из внешнего запроса выводится на экран.

Вывести имя и фамилию человека (FIRSTNAME, LASTNAME) из таблицы MAN, который проживал бы в городе с населением (PEOPLES) больше 1 миллиона человек. Использовать EXISTS.

Рисунок 113. Запрос c EXISTS к таблице MAN

Подзапрос EXISTS выбирает данные, связанные с внешним запросом по колонке CITYCODE, то есть для каждой возвращаемой строки внешнего запроса SELECT FIRSTNAME, LASTNAME FROM MAN m проверяется по CITYCODE наличие такой строки во внутреннем запросе SELECT 1 FROM CITY c WHERE c.CITYCODE = m.CITYCODE, в котором есть дополнительное условие на количество населения c.PEOPLES> 1 000 000. Если подзапрос находит такую строку, то строчка из внешнего запроса выводится на экран.

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

Также эффективно использовать EXIST с логическим операндом NOT, например, используя EXISTS, вывести на экран все города (*) из таблицы CITY, для которых нет соответствующей записи в таблице MAN, связь по полю CITYCODE.

Рисунок 114. Запрос c EXISTS к таблице CITY

В прошлом шаге вы говорили, что можно с помощью EXISTS заменить конструкцию IN. Приведите пример.

Например, у нас есть таблицы TABLE1, TABLE 2, поля связи KEY1, KEY2. Необходимо вывести на экран те данные из таблицы TABLE1, для которых присутствуют соответствующие строки в TABLE2 по KEY1, KEY2.

У вас в примерах есть синтаксис вида SELECT 1 FROM MAN m WHERE c.CITYCODE = m.CITYCODE. Почему именно единица? Поясните, как это работает.

Если в подзапросе есть хоть одна строка, то будет выведена единица, если нет, то подзапрос ничего не вернет, сама цифра 1 здесь не принципиальна.

1. Вывести все автомобили из таблицы AUTO, для которых нет соответствующих записей в таблице MAN. Использовать EXISTS (связь по PHONENUM).

2. Вывести все автомобили из таблицы AUTO, где есть записи в таблице MAN, где возраст людей больше 35 лет, используя EXISTS (связь по PHONENUM).

Помните перечисление колонок таблицы после команды SELECT? В языке SQL есть возможность задать новую колонку как результат выполнения подзапроса. Разберемся, как это сделать.

Возможности использования подзапросов.

Язык SQL позволяет использовать подзапрос для вывода значений в качестве дополнительных колонок запроса.

Синтаксис

SELECT колонка 1, колонка 2, колонка 3, (SELECT колонка FROM другая таблица WHERE таблица1.колонка связи = другая таблица. колонка связи) псевдоним колонки FROM

Помните, по каким колонкам объединяются таблицы в нашей схеме?

AUTO — > MAN = PHONENUM

CITY — > MAN = CITYCODE