Sqlзапросы, использующие exists


Содержание

SQL EXISTS Оператор

Оператор SQL EXISTS

Оператор EXISTS используется для проверки существования любой записи в подчиненном запросе.

Оператор EXISTS возвращает значение true, если вложенный запрос возвращает одну или несколько записей.

EXISTS Синтаксис

Демонстрационная база данных

Ниже приведен выбор из таблицы «Products» в образце базы данных Northwind:

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 — 12 oz bottles 19
3 Aniseed Syrup 1 2 12 — 550 ml bottles 10
4 Chef Anton’s Cajun Seasoning 2 2 48 — 6 oz jars 22
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 21.35

И выбор из таблицы «Поставщики»:

SupplierID SupplierName ContactName Address City PostalCode Country
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. London EC1 4SD UK
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA
3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA
4 Tokyo Traders Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo 100 Japan

Примеры, существующие в SQL

Следующая инструкция SQL возвращает true и перечисляет поставщиков с ценой продукта меньше 20:

Пример

Следующая инструкция SQL возвращает true и перечисляет поставщиков с ценой продукта, равной 22:

Типы Подзапросов. Оператор EXISTS

Рассмотрим, какие существуют типы подзапросов (однострочные и многострочные) и использование оператора EXISTS.

Однострочные подзапросы : Запросы, которые возвращают только одну строку из внутреннего оператора SELECT

Многострочные подзапросы : Запросы, которые возвращают больше чем одну строку из внутреннего оператора SELECT

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

Использование Оператора EXISTS

Оператор EXISTS используется в запросах, где результат запроса зависит от того, существуют ли определенные строки в таблице. Он оценивается как TRUE , если подзапрос возвращает по крайней мере одну строку.

Пример на рисунке выводит на экран отделы, у которых нет сотрудников. Для каждой строки в таблице DEPARTMENTS проверяется условие, существует ли строка в таблице EMPLOYEES , у которой есть тот же ID отдела. В случае, если такая строка не существует, условие удовлетворяется для исследуемой строки и она выбирается. Если же существует соответствующая строка в таблице EMPLOYEES , строка не выбирается.

SQL-запросы с использованием EXISTS и OR

У меня есть 4 таблицы

какой идентификатор должен делать, это вытащить пользователей, которые принадлежат Контактной группе 1 и 3, или контакт пользователя 1 (в таблице: user_contacts). Ниже приведен код, но он возвращает запрос пустым

sql exists subquery

3 ответа

5 Решение lc. [2012-12-20 05:38:00]

Вот как бы я это сделал и должен быть наиболее оптимальным:

Если вы хотите использовать EXISTS, вы можете, конечно, повернуть это, но запрос может не использовать индексы (вы можете удалить DISTICT для этого запроса):

Я предлагаю сделать EXPLAIN и посмотреть, какой из них лучше для вашей РСУБД.

3 Greg [2012-12-20 05:36:00]

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

У меня нет SQL передо мной, чтобы проверить это, но я думаю, что он получит правильные результаты

Оператор EXISTS используется для проверки существования любой записи в подзапросе.

Оператор EXISTS возвращает true, если подзапрос возвращает одну или несколько записей.

Следующий оператор SQL возвращает TRUE и перечисляет поставщиков с ценой продукта менее 20:

Sqlзапросы, использующие exists

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

Применение оператора имеет следующий формальный синтаксис:

Например, найдем всех покупателей из таблицы Customer, которые делали заказы:

Другой пример — найдем все товары из таблицы Products, на которые не было заказов в таблице Orders:

Стоит отметить, что для получения подобного результата ы могли бы использовать и опеатор IN :

Но поскольку при применении EXISTS не происходит выборка строк, то его использование более оптимально и эффективно, чем использование оператора IN.

SQL запрос с EXISTS/NOT EXISTS

Помогите пожалуйста составить SQL запрос с использованием EXISTS/NOT EXISTS. Имеется 3 таблицы:

где в ОстановкиПоМаршруту: внешние ключи Маршрут.ID и Остановка.ID являются составным ключем.

Необходимо: вывести маршруты, которые включают все остановки заданного маршрута, с использованием операторов EXISTS/NOT EXISTS.

Я сделал этот запрос без вышеперечисленных операторов, который выводит нужный мне результат (СУБД MySQL):

Запрос выводит все маршруты, которые включают все остановки маршрута «Районный». У меня есть множество примеров с использованием данных операторов, но не могу придумать даже саму логику запроса. Прошу, натолкните на нужные мысли

Оптимизация запроса mysql — exists vs in?

Есть два запроса — один с EXISTS, второй с IN.

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

Неважно, какой размер строки для поиска через LIKE, первый запрос по explain перебирает в book_codes порядка 10тыс записей, а второй — порядка 4мл по одному и тому же ключу id_book.
В book_codes > 4млн записей.

UPD:
Изначально я написала запрос через JOIN

Но на кодревью JOIN завернули и предложили

Explain всех трех запросов

UPD UPD
По полю bс.code тоже есть индекс

  • Вопрос задан 07 окт.
  • 121 просмотр

Неважно, какой размер строки для поиска через LIKE, первый запрос по explain перебирает в book_codes порядка 10тыс записей, а второй — порядка 4мл по одному и тому же ключу id_book.


Но на кодревью JOIN завернули и предложили

А как объяснили отказ от join в пользу in?

вот-вот, самая частая ошибка — предположение, что IN срабатывает быстрее

Возможно кто-то прочёл о том что джоины плохо и теперь их боится, но эта информация уже не актуальна много лет(я не говорю конечно о сотнях джоинов).
JOIN намного эффективнее разбирается анализатором запросов, чем подзапросы и ошибиться в построении запроса сложнее используя JOIN.
Но конечно, лучше написать несколько разных запросов и протестировать их, замерить время выполнения, оценить explain, чем просто выбирать на обум.

У меня было в практике, что подзапрос в
SELECT (SELECT. ) FROM table
был эффективнее JOIN но без тестов это было бы не реально проверить.

так в этом и загвоздка, что в случае с IN или JOIN скорость выполнения всегда приблизительно одинаковая, какой бы длины строчку в LIKE туда не поставили — около 2сек.

а вот EXISTS просто умирает с увеличением LIKE

По полю bс.code тоже есть индекс

nelauvetau, как я помню, EXISTS выполняется на каждую запись, то есть
в случае EXISTS будет так:
1. Выбираем запись из бд.
2. Вызываем подзапрос EXISTS.
3. Если в подзапросе вернули хотябы одну запись, пишем эту запись во временную таблицу, иначе откидываем
4. Повторить с пункта 1 пока не пройдём по всем записям.
5. Вывести то что насобирали.

В случае с IN/JOIN:
1. Выполняем подзапрос и храним результат во временной таблице.
2. Выбираем записи у которых bl.id есть в нашей временной таблице из пункта 1 (тут очень хорошо помогают индексы и они используются).
3. Выводим.

запросик на проде долго выполняется

когда в строке для поиска через LIKE один символ, но чем больше символов я ищу через LIKE, тем первый запрос медленнее.

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

То есть например у нас есть

и при %а% поиск остановится на первой строке, то при поиске %ад%, придётся впустую просканировать всю таблицу(и это для каждой записи из основной таблицы, то есть 4млн раз. ).

Операторы EXISTS и NOT EXISTS

Новосибирская государственная академия экономики и управления

ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ДИСЦИПЛИНЕ

«БАЗЫ ДАННЫХ»

Лабораторная работа N 7

«Язык баз данных SQL: команды манипуляции данными»

НОВОСИБИРСК 2000

SQL – это сокращенное название языка структурированных запросов (Structured Query Language). Из названия языка понятно, что его основное назначение заключается в формировании запросов на получение информации из базы данных. Команды на выборку данных составляют основу языка манипулирования данными DML — составной части языка SQL. Однако DML состоит не только из команд выборки данных из базы. Существуют также команды модификации данными, управления данными и другие.

В лабораторной работе рассматриваются базовые средства языка DML. В процессе выполнения лабораторной работы мы будем придерживаться стандарта SQL2.

В связи с тем, что SQL является объемным языком, будем рассматривать только основные команды. Различные специфические средства SQL рассматриваются в последующих лабораторных работах.

Для выполнения лабораторной работы требуется знание основ реляционной модели данных, основ реляционной алгебры и реляционного исчисления, принципов работы с СУБД MS SQL Server.

В результате выполнения лабораторной работы Вы освоите способы манипулирования данными с помощью команд языка SQL, рассмотрите диалект языка, реализованный в СУБД MS SQL Server.

ВВЕДЕНИЕ

SQL содержит широкий спектр возможностей манипуляции данными, как для создания запросов, так и для обновления базы данных. Эти воз­можности опираются только на логическую структуру базы данных, а не на ее физическую структуру, что согласуется с требованиями реляционной мо­дели.

Первоначально структура синтаксиса SQL была основана (или, по крайней мере, казалась основанной) на реляционном исчислении Кодда. Единственной поддерживаемой операцией реляционной алгебры было объе­динение.

В SQL2 в дополнение к аналогичному реляционному исчислению синтаксису, разработанному в предыдущем стандарте, напрямую реализованы операции объединение, пересечение, разность и соединение. Операции выбора, проектирования и произведения поддерживались (и продолжают поддерживаться) практически напрямую, в то время как операции деления и присвоения поддерживаются в более громоздкой форме.

Сначала мы опишем язык запросов SQL, а затем его операции ввода и изменения данных. Операции изменения данных будут описаны в послед­нюю очередь, поскольку их структура в определенной степени опирается на структуру языка запросов.

Простые запросы

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

Простой запрос. Запрос, который обращается только к одной таблице базы данных.

Запрос: Кто работает штукатурами?

WHERE SKILL_TYPE = ‘Штукатур’

Результат:

Этот запрос иллюстрирует три наиболее часто встречающиеся фразы SQL: SELECT, FROM и WHERE. Хотя в нашем примере мы поместили их на разные строки, они все могут стоять в одной строке. Они также могут поме­щаться с разными отступами, а слова внутри фраз могут разделяться произ­вольным числом пробелов. Рассмотрим характеристики каждой фразы.

Select. Фраза SELECT перечисляет столбцы, которые должны войти в результирующую таблицу. Это всегда столбцы некоторой реляционной таб­лицы. В нашем примере результирующая таблица состоит из одного столбца (NAME), но в общем случае она может содержать несколько столбцов; она также может содержать вычисленные значения или константы. Мы приве­дем примеры каждого из этих вариантов. Если результирующая таблица должна содержать более одного столбца, то все нужные столбцы перечисля­ются после команды SELECT через запятую. Например, фраза SELECT WORKER_ID, NAME выдаст в результате таблицу, состоящую из столбцов WORKER_ID и NAME.

Фраза SELECT. Задает столбцы результирующей таблицы.

From. Фраза FROM задает одну или более таблиц, к которым обраща­ется запрос. Все столбцы, перечисленные во фразах SELECT и WHERE, должны существовать в одной из таблиц, перечисленных в команде FROM. В SQL2 эти таблицы могут быть напрямую определены в схеме как базовые таблицы или представления данных, или же они сами могут быть не имею­щими имен таблицами, полученными в результате запросов SQL. В послед­нем случае запрос явно приводится в команде FROM.

Фраза FROM. Задает существующие таблицы, к которым обращается запрос.

Where. Фраза WHERE содержит условие. на основании которого выби­раются строки таблицы (таблиц). В нашем примере условие состоит в том, что столбец SKILL_TYPE должен содержать константу ‘Штукатур’, заклю­ченную в апострофы, как это всегда делается с текстовыми константами в SQL. Фраза WHERE — наиболее изменчивая команда SQL; она может со­держать множество разнообразных условий. Большая часть нашего изложе­ния будет посвящена иллюстрации различных конструкций, разрешенных в команде WHERE.

Фраза WHERE.Задает условие, на основании которого выбираются строки из заданных таблиц.

Приведенный выше запрос SQL обрабатывается системой в следующем порядке: FROM, WHERE, SELECT. To есть строки таблицы, указанной в ко­манде FROM, помещаются в рабочую область для обработки. Затем к каждой строке последовательно применяется фраза WHERE. Все строки, не удовле­творяющие условию WHERE, исключаются из рассмотрения. Затем те строки, которые удовлетворяют условию WHERE, обрабатываются командой SELECT. В нашем примере из каждой такой строки выбирается NAME, и все выбранные значения выводятся в качестве результатов запроса.

Запрос: Привести все данные о зданиях офисов.

WHERE TYPE = ‘Офис’

BLDG IDАДРЕСTYPEQLTY LEVELSTATUS

312 Ул.Вязов, 123 Офис 2 2

210 Березовая ул. 1011 Офис З 1

111 Осиновая ул. 1213 Офис 4 1

Звездочка (*) в команде SELECT означает «строка целиком». Это удобное сокращение, которым мы будем часто пользоваться.

Илон Маск рекомендует:  Что такое код yacc

Запрос: Какова недельная зарплата каждого электрика?

SELECT NAME, ‘Недельная зарплата = ‘, 40 * HRLY_RATE

WHERE SKILL_TYPE = ‘Электрик’

Результат:

М.Фарадей Недельная зарплата = 500.00

Х.Колумб Недельная зарплата = 620.00

Этот запрос иллюстрирует употребление и символьных констант (в на­шем примере ‘Недельная зарплата = ‘), и вычислений в команде SELECT, Внутри команды SELECT можно производить вычисления, в которых ис­пользуются числовые столбцы и числовые константы, а также стандартные арифметические операторы (+, -, *, /), сгруппированные по мере необходи­мости с помощью скобок. Мы также включили новую команду ORDER BY, которая сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу. Если вы хотите упорядочивать результаты по убыванию, то к команде нужно добавить DESC. Фраза ORDER BY может сортировать результаты по нескольким столбцам, по одним — в порядке возрастания, по другим — в порядке убывания. Первым указывается столбец первичного ключа сортировки.

Символьная константа. Константа, состоящая из букв, цифр и «специальных» символов.

Запрос: У кого почасовая ставка от 10 до 12 долларов?


WHERE HRLY_RATE > = 10 AND HRLY_RATE (не равно), , =). Для создания составных условий или для отрицания условия могут использо­ваться булевы операции AND (И), OR (ИЛИ) и NOT (HE). Для группировки условий, как обычно в языках программирования, могут использоваться скобки.

Операторы сравнения =, <>, , =.

Булевы операцииAND (И), OR (ИЛИ) и NOT (HE).

Для формулировки этого запроса также можно былоиспользоватьоператор BETWEEN (между):

WHERE HRLY_RATE BETWEEN 10 AND 12

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

Запрос: Перечислить штукатуров, кровельщиков и электриков.

WHERE SKILL_TYPE IN (‘Штукатур’, ‘Кровельщик’, ‘Электрик’)

WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1412 К.Немо 13.75 Штукатур 1520

2920 Р.Гаррет 10.00 Кровельщик 2920

1520 Г.Риковер 11.75 Штукатур 1520

1311 Х.Колумб 15.50 Электрик 1311

Этот запрос поясняет использование оператора сравнения IN (В). Усло­вие WHERE считается истинным, если тип специальности строки располо­жен внутри множества, указанного в скобках, то есть если тип специаль­ности — штукатур, кровельщик или электрик. Мы еще встретимся с опера­тором IN в подзапросах.

Предположим, что мы не можем точно вспомнить написание специаль­ности: «электрик» или «электронщик» или еще как-то. Символы шаблона, которые замещают неопределенные строки символов, облегчают поиск не­точного написания в запросе.

Символы шаблона.Символы, замещающие неопределенные строки символов.

Запрос: Перечислить работников, чей тип специальности начинается с «Элек».

WHERE SKILL_TYPE LIKE (‘Элек%’)

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Электрик 1311

1311 Х.Колумб 15.50 Электрик 1311

В SQL есть два символа шаблона: % (процент) и _ (подчеркивание). Подчеркивание замещает ровно один неопределенный символ. Процент за­мещает произвольное число символов, начиная с нуля. Когда используются символы шаблона, для сравнения символьных переменных с константами требуется оператор LIKE (как). Другие примеры:

NAME LIKE ‘__Колумб’

Условие в первом примере истинно, если NAME состоит из двух симво­лов, за которыми следует ‘Колумб’. В таблице WORKER все имена начина­ются с первого инициала и точки. Таким образом, при помощи этого усло­вия мы. найдем всех работников по фамилии «Колумб». Условие второго примера позволяет найти всех работников, чьи фамилии начинаются на бу­кву «К».

Запрос:Найти все работы, которые начинаются в течение ближайших двух недель.

WHERE START _DATE BETWEEN CURRENT_DATE AND

CURRENT_DATE + INTERVAL ’14’ DAY

Результат: (Предположим, что текущая дата CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Этот запрос иллюстрирует употребление оператора BETWEEN (между) со значениями типа date (дата) и interval (промежуток). CURRENT_DATE — это функция, всегда возвращающая значение сегодняшней даты. Выражение

CURRENT_DATE + INTERVAL ’14’ DAY

прибавляет двухнедельный промежуток к текущей дате. Таким образом, ASSIGNMENT выбирается (в предположении, что сегодня 10.10) в том слу­чае, если в ней значение столбца START_DATE лежит между 10.10 и 24.10. Из этого видно, что мы можем прибавлять к полям дат величины типа interval. Более того, мы можем умножать значения промежутков на целые величины. Например, предположим, что мы хотим выяснить, какое число будет через определенное количество недель (обозначенное переменной NUM_WEEKS (ЧИСЛО НЕДЕЛЬ)). Мы можем это сделать так:

CURRENT_DATE + INTERVAL ‘7’ DAY * NUM_WEEKS

2. Многотабличные запросы

Возможность связывать элементы данных вне границ одной таблицы важна для любого языка баз данных. В реляционной алгебре эту функцию выполняет операция соединения. Хотя значительная часть SQL основана непосредственно на реляционном исчислении, SQL связывает данные разных таблиц аналогично тому, как это делает операция соединения реляционной алгебры. Сейчас мы покажем, как это делается. Рассмотрим запрос:

Запрос: Каковы специальности рабочих, назначенных на здание 435?

Данные, необходимые для ответа, находятся в двух таблицах: WORKER и ASSIGNMENT. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:

FROM WORKER, ASSIGNMENT

Что здесь происходит? Мы должны рассмотреть два этапа обработки сис­темой данного запроса.

1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом слу­чае, поскольку в команде указаны две таблицы, система создает декар­тово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таб­лиц, в которой каждая строка одной таблицы спарена с каждой стро­кой другой таблицы. В нашем примере, поскольку в таблице WORKER пять столбцов, а в таблице ASSIGNMENT четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m * n, где m — число строк таблицы WORKER; а n — число строк таблицы ASSIGNMENT. По­скольку в таблице WORKER 7 строк, а в таблице ASSIGNMENT 19 строк, то декартово произведение будет содержать 7х19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произве­дение всех таблиц, указанных в команде.

Декартово произведение. Результат объединения каждой строки одной таблицы с каждой строкой другой таблицы.

2. После создания гигантской реляционной таблицы система, как и пре­жде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM. проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строкам применяется фраза SELECT.

Фраза WHERE в нашем запросе содержит два условия:

1. WORKER. WORKER_ >

Первое из этих условий — условие соединения. Обратите внимание, что поскольку обе таблицы WORKER и ASSIGNMENT содержат столбец с име­нем WORKER_ID, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.

Первое условие означает, что в любой выбранной строке значение столбца WORKER_ >

Рис. 1. Соединение таблиц WORKER и ASSIGNMENT

Теперь мы покажем, как в SQL присоединить таблицу к ней самой.

Запрос: Перечислить работников, указав имена их менеджеров.

SELECT А.WORKER_NAME, B.WORKER_NAME

FROM WORKER A, WORKER В

Фраза FROM в этом примере создает две «копии» таблицы WORKER, давая им псевдонимы А и В. Псевдоним — это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства WORKER_ID в В и SUPV_ID в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.2).

Рис. 2. Соединение двух копий таблицы WORKER

Выбирая из каждой строки два имени работника, мы получим требуемый список:


К.Немо Г.Риковер Р.Гаррет Р.Гаррет

П.Мэйсон П.Мэйсон Г.Риковер Г.Риковер Х.Колумб Х.Колумб Дж.Барристер П.Мэйсон

Псевдоним. Альтернативное имя, данное таблице.

A.WORKER_NAME представляет работника, a B.WORKER_NAME пред­ставляет менеджера. Обратите внимание, что некоторые работники — сами себе менеджеры, что следует из выполненного в их строках равенства WORKER_ID — SUPV_ID.

В SQL можно за один раз связать более двух таблиц:

Запрос: Перечислить работников, назначенных на здания офисов.

FROM WORKER, ASSIGNMENT, BUILDING

NAME

Обратите внимание, что если имя столбца (например, WORKER_ID или BLDG_ID) встречается более, чем в одной таблице, то для избежания неопределенности мы должны перед именем столбца указать имя исходной таблицы. Но если имя столбца встречается только в одной таблице, как TYPE в нашем примере, то никакой неопределенности нет, поэтому имя таблицы указывать не нужно.

Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по WORKER_ID, после чего к полученной таблице присоединяется третья таблица по BLDG_ID. Условие

команды WHERE приводит к исключению всех строк, кроме строк, относящихся к офисным зданиям. Это соответствует требованиям запроса.

Подзапрос.Запрос внутри запроса

Подзапрос может помещаться в команду WHERE запроса, в результате чего возможности команды WHERE расширяются. Рассмотрим пример.

Запрос: Каковы специальности рабочих, назначенных на здание 435?

FROM WORKER WHERE WORKER_ID IN

Подзапрос в этом примере

Запрос, в котором содержится подзапрос, называется внешним запросом или главным запросом. Подзапрос приводит к созданию следующего множе­ства ИД (идентификаторов) работников:

Внешний запрос. Главный запрос, в котором содержатся все подзапросы.

Затем это множество ИД занимает место подзапроса во внешнем запросе. С этого момента выполняется внешний запрос, использующий множество, созданное подзапросом. Внешний запрос обрабатывает каждую строку таб­лицы WORKER в соответствии с условием WHERE. Если WORKER_ID строки лежит в (IN) множестве, созданном подзапросом, то SKILL_TYPE строки выбирается и выводится в результирующей таблице:

Очень важно, что фраза SELECT подзапроса содержит WORKER_ID и только WORKER_ID. В противном случае фраза WHERE внешнего запроса, означающая, что WORKER_ID лежит в множестве ИД работников, не имела бы смысла.

Обратите внимание, что подзапрос может логично выполняться прежде, чем хотя бы одна строка рассматривается главным запросом. В некотором смысле подзапрос независим от главного запроса. Он может выполняться как полноценный запрос. Мы говорим, что такой подзапрос не коррелирован с главным запросом. Как мы вскоре увидим, подзапросы могут быть коррелированными.

Некоррелированный подзапрос.Подзапрос, значение которого не зависит ни от какого внешнего запроса.

Приведем пример подзапроса внутри подзапроса.

Запрос: Перечислить работников, назначенных на здания офисов.

Снова мы рассматриваем запрос, с помощью которого мы изучали соединение.

Руководство по SQL: Как лучше писать запросы (Часть 1)

Узнайте о антипаттернах, планах выполнения, time complexity, настройке запросов и оптимизации в SQL

Язык структурированных запросов (SQL) является незаменимым навыком в индустрии информатики, и вообще говоря, изучение этого навыка относительно просто. Однако большинство забывают, что SQL — это не только написание запросов, это всего лишь первый шаг дальше по дороге. Обеспечение производительности запросов или их соответствия контексту, в котором вы работаете, — это совсем другая вещь.

Вот почему это руководство по SQL предоставит вам небольшой обзор некоторых шагов, которые вы можете пройти, чтобы оценить ваш запрос:

  • Во-первых, вы начнете с краткого обзора важности обучения SQL для работы в области науки о данных;
  • Далее вы сначала узнаете о том, как выполняется обработка и выполнение запросов SQL, чтобы понять важность создания качественных запросов. Конкретнее, вы увидите, что запрос анализируется, переписывается, оптимизируется и окончательно оценивается.
  • С учетом этого, вы не только перейдете к некоторым антипаттернам запросов, которые начинающие делают при написании запросов, но и узнаете больше об альтернативах и решениях этих возможных ошибок; Кроме того, вы узнаете больше о методическом подходе к запросам на основе набора.
  • Вы также увидите, что эти антипаттерны вытекают из проблем производительности и что, помимо «ручного» подхода к улучшению SQL-запросов, вы можете анализировать свои запросы также более структурированным, углубленным способом, используя некоторые другие инструменты, которые помогают увидеть план запроса; И,
  • Вы вкратце узнаете о time complexity и big O notation, для получения представления о сложности плана выполнения во времени перед выполнением запроса;
  • Вы кратко узнаете о том, как оптимизировать запрос.

Почему следует изучать SQL для работы с данными?

SQL далеко не мертв: это один из самых востребованных навыков, который вы находите в описаниях должностей из индустрии обработки и анализа данных, независимо от того, претендуете ли вы на аналитику данных, инженера данных, специалиста по данным или на любые другие роли. Это подтверждают 70% респондентов опроса О ‘Рейли (O’ Reilly Data Science Salary Survey) за 2020 год, которые указывают, что используют SQL в своем профессиональном контексте. Более того, в этом опросе SQL выделяется выше языков программирования R (57%) и Python (54%).

Вы получаете картину: SQL — это необходимый навык, когда вы работаете над получением работы в индустрии информатики.

Неплохо для языка, который был разработан в начале 1970-х, верно?

Но почему именно так часто используется? И почему он не умер, несмотря на то, что он существует так долго?

Есть несколько причин: одной из первых причин могло бы стать то, что компании в основном хранят данные в реляционных системах управления базами данных (RDBMS) или в реляционных системах управления потоками данных (RDSMS), и для доступа к этим данным нужен SQL. SQL — это lingua franca данных: он дает возможность взаимодействовать практически с любой базой данных или даже строить свою собственную локально!

Если этого еще недостаточно, имейте в виду, что существует довольно много реализаций SQL, которые несовместимы между вендорами и не обязательно соответствуют стандартам. Знание стандартного SQL, таким образом, является для вас требованием найти свой путь в индустрии (информатики).

Кроме того, можно с уверенностью сказать, что к SQL также присоединились более новые технологии, такие как Hive, интерфейс языка запросов, похожий на SQL, для запросов и управления большими наборами данных, или Spark SQL, который можно использовать для выполнения запросов SQL. Опять же, SQL, который вы там найдете, будет отличаться от стандарта, который вы могли бы узнать, но кривая обучения будет значительно проще.

Если вы хотите провести сравнение, рассматривайте его как обучение линейной алгебре: приложив все эти усилия в этот один предмет, вы знаете, что вы сможете использовать его, чтобы также освоить машинное обучение!

Короче говоря, вот почему вы должны изучить этот язык запросов:

  • Его довольно легко освоить, даже для новичков. Кривая обучения довольно проста и постепенна, поэтому вы будете писать запросы в кратчайшие сроки.
  • Он следует принципу «учись один раз, используй везде», так что это отличное вложение твоего времени!
  • Это отличное дополнение к языкам программирования; В некоторых случаях написание запроса даже предпочтительнее написания кода, потому что он более производительный!
  • .

Чего вы все еще ждете? :)

Обработка SQL и выполнение запросов

Чтобы повысить производительность вашего SQL-запроса, вы сначала должны знать, что происходит внутри, когда вы нажимаете ярлык для выполнения запроса.

Сначала запрос разбирается в «дерево разбора» (parse tree); Запрос анализируется на предмет соответствия синтаксическим и семантическим требованиям. Синтаксический анализатор создает внутреннее представление входного запроса. Затем эти выходные данные передаются в механизм перезаписи.

Илон Маск рекомендует:  Что такое код fbsql_next_result

Затем оптимизатор должен найти оптимальное выполнение или план запроса для данного запроса. План выполнения точно определяет, какой алгоритм используется для каждой операции, и как координируется выполнение операций.

Чтобы найти наиболее оптимальный план выполнения, оптимизатор перечисляет все возможные планы выполнения, определяет качество или стоимость каждого плана, принимает информацию о текущем состоянии базы данных, а затем выбирает наилучший из них в качестве окончательного плана выполнения. Поскольку оптимизаторы запросов могут быть несовершенными, пользователям и администраторам баз данных иногда приходится вручную изучать и настраивать планы, созданные оптимизатором, чтобы повысить производительность.

Теперь вы, вероятно, задаетесь вопросом, что считается «хорошим планом запроса».

Как вы уже читали, качество стоимости плана играет немаловажную роль. Более конкретно, такие вещи, как количество дисковых операций ввода-вывода (disk I/Os), которые требуются для оценки плана, стоимость CPU плана и общее время отклика, которое может наблюдать клиент базы данных, и общее время выполнения, имеют важное значение. Вот тут-то и возникнет понятие сложности времени (time complexity). Подробнее об этом вы узнаете позже.

Затем выбранный план запроса выполняется, оценивается механизмом выполнения системы и возвращаются результаты запроса.

Написание SQL-запросов

Из предыдущего раздела, возможно, не стало ясно, что принцип Garbage In, Garbage Out (GIGO) естественным образом проявляется в процессе обработки и выполнения запроса: тот, кто формулирует запрос, также имеет ключи к производительности ваших запросов SQL. Если оптимизатор получит плохо сформулированный запрос, он сможет сделать только столько же…

Это означает, что есть некоторые вещи, которые вы можете сделать, когда пишете запрос. Как вы уже видели во введении, ответственность тут двоякая: речь идет не только о написании запросов, которые соответствуют определенному стандарту, но и о сборе идей о том, где проблемы производительности могут скрыться в вашем запросе.

Идеальная отправная точка — подумать о «местах» в ваших запросах, где могут возникнуть проблемы. И, в общем, есть четыре ключевых слова, в которых новички могут ожидать возникновения проблем с производительностью:

  • Условие WHERE ;
  • Любые ключевые слова INNER JOIN или LEFT JOIN ; А также,
  • Условие HAVING ;

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

Тем не менее, вы также должны понимать, что производительность — это нечто, что должно стать значимым. Однако просто сказать, что эти предложения и ключевые слова плохи — это не то, что нужно, когда вы думаете о производительности SQL. Наличие предложения WHERE или HAVING в запросе не обязательно означает, что это плохой запрос…

Ознакомьтесь со следующим разделом, чтобы узнать больше об антипаттернах и альтернативных подходах к построению вашего запроса. Эти советы и рекомендации предназначены в качестве руководства. То, как и если вам действительно нужно переписать ваш запрос, зависит, помимо прочего, от количества данных, базы данных и количества раз, которое вам нужно для выполнения запроса. Это полностью зависит от цели вашего запроса и иметь некоторые предварительные знания о базе данных, с которой вы будете работать, имеет решающее значение!

1. Извлекайте только необходимые данные

Умозаключение «чем больше данных, тем лучше» — не обязательно должна соблюдаться при написании SQL: вы рискуете не только запутаться, получив больше данных, чем вам действительно нужно, но и производительность может пострадать от того, что ваш запрос получает слишком много данных.

Вот почему, как правило, стоит обратить внимание на оператор SELECT , предложение DISTINCT и оператор LIKE .


Оператор SELECT

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

Если у вас есть коррелированные подзапросы с EXISTS , вы должны попытаться использовать константу в операторе SELECT этого подзапроса вместо выбора значения фактического столбца. Это особенно удобно, когда вы проверяете только существование.

Помните, что коррелированный подзапрос является подзапросом, использующим значения из внешнего запроса. И обратите внимание, что, несмотря на то, что NULL может работать в этом контексте как «константа», это очень запутанно!

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

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

Операция DISTINCT

Инструкция SELECT DISTINCT используется для возврата только различных значений. DISTINCT — это пункт, которого, безусловно, следует стараться избегать, если можно. Как и в других примерах, время выполнения увеличивается только при добавлении этого предложения в запрос. Поэтому всегда полезно рассмотреть, действительно ли вам нужна эта операция DISTINCT , чтобы получить результаты, которые вы хотите достичь.

Оператор LIKE

При использовании оператора LIKE в запросе индекс не используется, если шаблон начинается с % или _ . Это не позволит базе данных использовать индекс (если он существует). Конечно, с другой точки зрения, можно также утверждать, что этот тип запроса потенциально оставляет возможность для получения слишком большого количества записей, которые не обязательно удовлетворяют цели запроса.

Опять же, знание данных, хранящихся в базе данных, может помочь вам сформулировать шаблон, который будет правильно фильтровать все данные, чтобы найти только строки, которые действительно важны для вашего запроса.

2. Ограничьте свои результаты

Если вы не можете избежать фильтрации вашего оператора SELECT , вы можете ограничить свои результаты другими способами. Вот здесь и подходят такие подходы, как предложение LIMIT и преобразования типов данных.

Операторы TOP , LIMIT и ROWNUM

Можно добавить операторы LIMIT или TOP в запросы, чтобы задать максимальное число строк для результирующего набора. Вот несколько примеров:

Обратите внимание, что вы можете дополнительно указать PERCENT , например, если вы измените первую строку запроса с помощью SELECT TOP 50 PERCENT * .

Кроме того, можно добавить предложение ROWNUM , эквивалентное использованию LIMIT в запросе:

Преобразования типов данных

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

Однако при добавлении преобразования типа данных в запрос увеличивается только время выполнения.

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

3. Не делайте запросы более сложными, чем они должны быть

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

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

Оператор OR

Когда вы используете оператор OR в своем запросе, скорее всего, вы не используете индекс.

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

Если вы не используете индексы, включенные в базу данных, выполнение вашего запроса неизбежно займет больше времени. Вот почему лучше всего искать альтернативы использованию оператора OR в вашем запросе;

Рассмотрим следующий запрос:

Оператор можно заменить на:

Условие с IN ; или

Две инструкции SELECT с UNION .

Совет: здесь вы должны быть осторожны, чтобы не использовать ненужную операцию UNION , потому что вы просматриваете одну и ту же таблицу несколько раз. В то же время вы должны понимать, что когда вы используете UNION в своем запросе, время выполнения увеличивается. Альтернативы операции UNION : переформулировка запроса таким образом, чтобы все условия были помещены в одну инструкцию SELECT , или использование OUTER JOIN вместо UNION .

Совет: имейте также в виду, что, хотя OR — и другие операторы, которые будут упомянуты в следующих разделах — скорее всего, не используют индекс, поиск по индексу не всегда предпочтителен!

Оператор NOT

Когда ваш запрос содержит оператор NOT , вполне вероятно, что индекс не используется, как и с оператором OR . Это неизбежно замедлит ваш запрос. Если вы не знаете, что здесь подразумевается, рассмотрите следующий запрос:

Этот запрос, безусловно, будет выполняться медленнее, чем вы, возможно, ожидаете, в основном потому, что он сформулирован гораздо сложнее, чем может быть: в таких случаях, как этот, лучше всего искать альтернативу. Рассмотрите возможность замены NOT операторами сравнения, такими как > , <> или !> ; Приведенный выше пример действительно может быть переписан и выглядеть примерно так:

Это уже выглядит лучше, не так ли?

Оператор AND

Оператор AND — это другой оператор, который не использует индекс и который может замедлить запрос, если он используется чрезмерно сложным и неэффективным образом, как в следующем примере:

Лучше переписать этот запрос, используя оператор BETWEEN :

Операторы ANY и ALL

Кроме того, операторы ANY и ALL — это те операторы, с которыми вам следует быть осторожным, поскольку, если включить их в свои запросы, индекс не будет использоваться. Здесь пригодятся альтернативные функции агрегирования, такие как MIN или MAX .

Совет: в тех случаях, когда вы используете предлагаемые альтернативы, вы должны знать о том, что все функции агрегации, такие как SUM , AVG , MIN , MAX над многими строками, могут привести к длительному запросу. В таких случаях можно попытаться минимизировать количество строк для обработки или предварительно вычислить эти значения. Вы еще раз видите, что важно знать о своей среде, своей цели запроса,… Когда вы принимаете решение о том, какой запрос использовать!

Изолируйте столбцы в условиях

Также в случаях, когда столбец используется в вычислении или в скалярной функции, индекс не используется. Возможным решением было бы просто выделить конкретный столбец, чтобы он больше не был частью вычисления или функции. Рассмотрим следующий пример:

Это выглядит забавно, а? Вместо этого попробуйте пересмотреть расчет и переписать запрос примерно так:

4. Отсутствие грубой силы

Этот последний совет означает, что не следует пытаться ограничить запрос слишком сильно, так как это может повлиять на его производительность. Это особенно справедливо для соединений и для предложения HAVING.

Порядок таблиц в соединениях

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

Избыточные условия при соединениях

При добавлении слишком большого количества условий к соединениям SQL обязан выбрать определенный путь. Однако может быть, что этот путь не всегда является более эффективным.

Условие HAVING

Условие HAVING было первоначально добавлено в SQL, так как ключевое слово WHERE не могло использоваться с агрегатными функциями. HAVING обычно используется с операцией GROUP BY , чтобы ограничить группы возвращаемых строк только теми, которые удовлетворяют определенным условиям. Однако, если это условие используется в запросе, индекс не используется, что, как вы уже знаете, может привести к тому, что запрос на самом деле не так хорошо работает.

Если вы ищете альтернативу, попробуйте использовать условие WHERE .

Рассмотрим следующие запросы:

Первый запрос использует предложение WHERE , чтобы ограничить количество строк, которые необходимо суммировать, тогда как второй запрос суммирует все строки в таблице, а затем использует HAVING , чтобы отбросить вычисленные суммы. В таких случаях вариант с предложением WHERE явно лучше, так как вы не тратите ресурсы.

Видно, что речь идет не об ограничении результирующего набора, а об ограничении промежуточного числа записей в запросе.

Следует отметить, что различие между этими двумя условиями заключается в том, что предложение WHERE вводит условие для отдельных строк, в то время как предложение HAVING вводит условие для агрегаций или результатов выбора, где один результат, такой как MIN , MAX , SUM ,… был создан из нескольких строк.

Вы видите, оценка качества, написание и переписывание запросов не является простой задачей, если учесть, что они должны быть максимально производительными; Предотвращение антипаттернов и рассмотрение альтернативных вариантов также будут частью ответственности при написании запросов, которые необходимо выполнять на базах данных в профессиональной среде.

Этот список был лишь небольшим обзором некоторых антипаттернов и советов, которые, надеюсь, помогут начинающим; Если вы хотите получить представление о том, что более старшие разработчики считают наиболее частыми антиобразцами, ознакомьтесь с этим обсуждением.


Set-based против процедурных подходов к написанию запросов

В вышеприведенных антипаттернах подразумевалось то, что они фактически сводятся к разнице в основанных на наборах и процедурных подходах к построению ваших запросов.

Процедурный подход к запросам — это подход, очень похожий на программирование: вы говорите системе, что делать и как это делать.

Примером этого являются избыточные условия в соединениях или случаи, когда вы злоупотребляете условями HAVING , как в приведенных выше примерах, в которых вы запрашиваете базу данных, выполняя функцию и затем вызывая другую функцию, или вы используете логику, содержащую условия, циклы, пользовательские функции (UDF), курсоры,… чтобы получить конечный результат. При таком подходе вы часто будете запрашивать подмножество данных, затем запрашивать другое подмножество данных и так далее.

Неудивительно, что этот подход часто называют «пошаговым» или «построчным» запросом.

Другой подход — подход, основанный на наборе, где вы просто указываете, что делать. Ваша роль состоит в указании условий или требований для результирующего набора, который вы хотите получить из запроса. То, как ваши данные извлекаются, вы оставляете внутренним механизмам, которые определяют реализацию запроса: вы позволяете ядру базы данных определять лучшие алгоритмы или логику обработки для выполнения вашего запроса.

Поскольку SQL основан на наборах, неудивительно, что этот подход будет более эффективным, чем процедурный, и он также объясняет, почему в некоторых случаях SQL может работать быстрее, чем код.

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

Илон Маск рекомендует:  Как изменить вид нажатой кнопки

Обратите внимание, что если вам когда либо понадобится процедурный запрос, вы должны рассмотреть возможность его переписывания или рефакторинга.

В следующей части будут рассмотрены план и оптимизация запросов

Образовательный блог — всё для учебы

1) Вложенные подзапросы

SQL позволяет вкладывать запросы друг в друга. Обычно подзапрос возвращает одно значение, которое проверяется на предмет истинности предиката.

Виды условий поиска:
• Сравнение с результатом вложенного запроса (=, <>, , >=)
• Проверка на принадлежность результатам подзапроса (IN)
• Проверка на существование (EXISTS)
• Многократное (количественное) сравнение (ANY, ALL)

Примечания по вложенным запросам:
• Подзапрос должен выбирать только один столбец (за исключением подзапроса с предикатом EXISTS), и тип данных его результата должен соответствовать типу данных значения, указанному в предикате.
• В ряде случаев можно использовать ключевое слово DISTINCT для гарантии получения единственного значения.
• Во вложенном запросе нельзя включать раздел ORDER BY и UNION.
• Подзапрос может находиться и лева и справа от условия поиска.
• В подзапросах могут использоваться функции агрегирования без раздела GROUP BY, которые автоматически выдают специальное значение для любого количества строк, специальный предикат IN, а также выражения, основанные на столбцах.
• По возможности следует вместо подзапросов использовать соединение таблиц JOIN.

Примеры на вложенные запросы:

SELECT * FROM Orders WHERE SNum=(SELECT SNum FROM SalesPeople WHERE SName=’Motika’)
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City=’London’)
SELECT * FROM Orders WHERE SNum=(SELECT DISTINCT SNum FROM Orders WHERE CNum=2001)
SELECT * FROM Orders WHERE Amt>(SELECT AVG(Amt) FROM Orders WHERE Odate=10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName=’Serres’)

2) Связанные подзапросы

В SQL можно создавать подзапросы со ссылкой на таблицу из внешнего запроса. В этом случае подзапрос выполняется многократно, по одному разу для каждой строки таблицы из внешнего запроса. Поэтому важно, чтобы подзапрос использовал индекс. Подзапрос может обращаться к той же таблице, чтоб и внешний. Если внешний запрос возвращает относительно небольшое число строк, то связанный подзапрос будет работать быстрее несвязанного. Если подзапрос возвращает небольшое число строк, то связанный запрос выполнится медленнее несвязанного.

Примеры на связанные подзапросы:

SELECT * FROM SalesPeople Main WHERE 1 (SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //возвращает все заказы, величина которых превосходит среднюю величины заказа для данного покупателя

3) Предикат EXISTS

Синтаксическая форма: [NOT] EXISTS ( )

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

Примечания по предикату EXISTS:
• EXISTS – предикат, возвращающий значение TRUE или FALSE, и его можно применять отдельно или вместе с другими булевыми выражениями.
• EXISTS не может использовать функции агрегирования в своем подзапросе.
• В коррелирующих (связанных, зависимых – Correlated) подзапросах предикат EXISTS выполняется для каждой строки внешней таблицы.
• Можно комбинировать предикат EXISTS с соединениями таблиц.

Примеры на предикат EXISTS:

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Customer WHERE City=’San Jose’) – возвращает всех покупателей, если кто-то из них проживает в San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum=First.SNum AND Send.CNum<>First.CNum) – возвращает номера продавцов, обслуживших только одного покупателя.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNum<>T.CNum AND F.SNum=S.SNum) – возвращает номера, имена и города проживания всех продавцов, обслуживших нескольких покупателей.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1 [NOT] <=|>| =| > ANY|ALL ( )

Эти предикаты используют в качестве аргумента подзапрос, однако, по сравнению с предикатом EXISTS, они применяются в конъюнкции с предикатами отношения (=,<>, ,>=). В этом смысле они сходны с предикатом IN, но применяются только с подзапросами. Стандарт допускает использовать вместо ANY ключевое слово SOME, однако не все СУБД его поддерживают.

Примечания по предикатам сравнения:
• Предикат ALL принимает значение TRUE, если каждое значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
• Предикат ANY принимает значение TRUE, если хотя бы одно значение, выбранное в процессе выполнения подзапроса, удовлетворяет условию, заданному в предикате внешнего запроса. Чаще всего он используется с неравенствами.
• Если подзапрос не возвращает строк, то ALL автоматически принимает значение TRUE (считается, что условие сравнения выполняется), а для ANY – FALSE.
• Если сравнение не имеет значения TRUE ни для одной строки и есть одна или несколько строк с NULL значением, то ANY возвращает UNKNOWN.
• Если сравнение не имеет значения FALSE ни для одной строки и есть одна или несколько строк с NULL значением, то ALL возвращает UNKNOWN.

Примеры на предикат количественного сравнения:

SELECT * FROM SalesPeople WHERE City=ANY(SELECT City FROM Customer)
SELECT * FROM Orders WHERE AmtALL(SELECT Rating FROM Customer WHERE City=’Rome’)

5) Предикат уникальности

UNIQUE|DISTINCT ( )

Предикат служит для проверка уникальности (отсутствия дублей) в выходных данных подзапроса. Причем в предикате UNIQUT строки с NULL значениями считаются уникальными, а в предикате DISTINCT два неопределенных значения считаются равными друг другу.

6) Предикат совпадений

MATCH [UNIQUE] [PARTIAL|FULL] ( )

Предикат MATCH проверяет, будет ли значение строки запроса совпадать со значением любой строки, полученной в результате подзапроса. От предикатов IN И ANY такой подзапрос отличается тем, что позволяет обрабатывать «частичные» (PARTIAL) совпадения, которые могут встречаться среди строк, имеющих часть NULL-значений.

7) Запросы в разделе FROM

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

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City=’London’ AND Customer.CNum=Orders.CNum
//подзапрос возвращает суммарную величину заказов, сделанных каждым покупателем из Лондона.

8) Рекурсивные запросы

WITH RECURSIVE
Q1 AS SELECT … FROM … WHERE …
Q2 AS SELECT … FROM … WHERE …

ИСПОЛЬЗОВАНИЕ ОПЕРАТОРА EXISTS

ТЕПЕРЬ, КОГДА ВЫ ХОРОШО ОЗНАКОМЛЕНЫ С ПОДЗАПРОСАМИ, мы можем говорить о некоторых специальных операторах которые всегда берут подзапросы как аргументы. Вы узнаете о первом из их в этой главе. Остальные будут описан в следующей главе.

Оператор EXISTS используется чтобы указать предикату, — производить ли подзапросу вывод или нет. В этой главе, вы узнаете как использовать этот оператор со стандартными и ( обычно ) соотнесенными подзапросами. Мы будем также обсуждать специальные размышления которые перейдут в игру когда вы будете использовать этот оператор как относительный агрегат, как пустой указатель NULL, и как оператор Бул. Кроме того, вы можете повысить ваш профессиональный уровень относительно подзапросов исследуя их в более сложных прикладных программах чем те которые мы видели до сих пор.

КАК РАБОТАЕТ EXISTS?

EXISTS — это оператор, который производит верное или неверное значение, другими словами, выражение Бул ( см. Главу 4 для обзора этого термина ).

Это означает что он может работать автономно в предикате или в комбинации с другими выражениями Бул использующими Булевые операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose ( вывод для этого запроса показывается в Рисунке 12.1 ): Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос( не соотнесенный ) был выполнен только один раз для всего внешнего запроса, и следовательно Для каждой строки-кандидата внешнего запроса ( представляющей заказчика проверяемого в настоящее время ), внутренний запрос находит строки которые совпадают со значением пол snum ( которое имел продавец ), но не со значением пол cnum ( соответствующего другим заказчикам ). Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика обслуживаемых текущим продавцом (то есть продавцом заказчика в текущей строке-кандидата из внешнего запроса ). Предикат EXISTS поэтому верен для текущей строки, и номер продавца пол (snum) таблицы указанной во внешнем запросе будет выведено. Если был DISTINCT не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика к которому он назначен.

КОМБИНАЦИЯ ИЗ EXISTS И ОБЪЕДИНЕНИЯ

Однако для нас может быть полезнее вывести больше информации об этих продавцах а не только их номера. Мы можем сделать это объединив таблицу Заказчиков с таблицей Продавцов ( вывод для запроса показывается в Рисунке 12.3 ): Внутренний запрос здесь — как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос — это объединение таблицы Продавцов с таблицей Заказчиков, наподобие того что мы видели прежде. Новое предложение основного предиката ( AND first.snum = second.snum ) естественно оценивается на том же самом уровне что и предложение EXISTS. Это — функциональный предикат самого объединения, сравнивающий две таблицы из внешнего запроса в терминах пол snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть верны в порядке для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях когда вторая часть запроса верна, а объединение — выполнимо. Таким образом, комбинация объединения и подзапроса может стать очень мощным способом обработки данных.

ИСПОЛЬЗОВАНИЕ NOT EXISTS

Предыдущий пример дал понять что EXISTS может работать в комбинации с операторами Бул. Конечно, то что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS — это оператор NOT. Один из способов которым мы могли бы найти всех продавцов только с одним заказчиком будет состоять в том, чтобы инвертировать наш предыдущий пример. (Вывод для этого запроса показывается в Рисунке 12.4:)

EXISTS И АГРЕГАТЫ

Одна вещь которую EXISTS не может сделать — взять функцию агрегата в подзапросе. Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это — значение функции ; если же агрегатная функция не находит никаких строк, EXISTS неправилен. Попытка использовать агрегаты с EXISTS таким способом, вероятно покажет что проблема неверно решалась от начала до конца.

Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели ( или который мы будем видеть ). Такие подзапросы, и любые другие в них, позволяют использовать агрегаты, если нет другой причины по которой они не могут быть использованы. Следующий раздел приводит этому пример.

В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат — EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) — будет эквивалентен — EXISTS (SELECT sname FROM Salespeople) который был позволен выше.

БОЛЕЕ УДАЧНЫЙ ПРИМЕР ПОДЗАПРОСА

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

Имеется запрос который извлекает строки всех продавцов которые имеют заказчиков с больше чем одним текущим порядком. Это не обязательно самое простое решение этой проблемы, но оно предназначено скорее показать улучшенную логику SQL. Вывод этой информации связывает все три наши типовых таблицы: Мы могли бы разобрать вышеупомянутый запрос примерно так:

Берем каждую строку таблицы Продавцов как строку-кандидат( внешний запрос ) и выполняем подзапросы. Для каждой строки-кандидата из внешнего запроса, берем в соответствие каждую строку из таблицы Заказчиков( средний запрос ). Если текущая строка заказчиков не совпадает с текущей строкой продавца( т.е. если first.snum second.snum ), предикат среднего запроса неправилен. Всякий раз, когда мы находим заказчика в среднем запросе который совпадает с продавцом во внешнем запросе, мы должны рассматривать сам внутренний запрос чтобы определить, будет ли наш средний предикат запроса верен. Внутренний запрос считает число порядков текущего заказчика ( из среднего запроса ). Если это число больший чем 1, предикат среднего запроса верен, и строки выбираются. Это делает EXISTS предикат внешнего запроса верным для текущей строки продавца, и означает, что по крайней мере один из текущих заказчиков продавца имеет более чем один порядок.

Если это не кажется достаточно понятным для вас в этой точке разбора примера, не волнуйтесь. Сложность этого примера — хороша независимо от того, как часто будете Вы использовать ее в деловой ситуации. Основная цель примеров такого типа состоит в том, чтобы показать вам некоторые возможности которые могут оказаться в дальнейшем полезными. После работы со сложными ситуациями подобно этой, простые запросы которые являются наиболее часто используемыми в SQL, покажутся Вам элементарными.

Кроме того, этот запрос, даже если он кажется удобным, довольно извилистый способ извлечения информации и делает много работы. Он связывает три разных таблицы чтобы дать вам эту информацию, а если таблиц больше чем здесь указано, будет трудно получить ее напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL).Возможно вам нужно увидеть эту информацию относительно регулярной основы — если, например, вы имеете премию в конце недели для продавца который получил многочисленные порядки от одного заказчика. В этом случае, он должен был бы вывести команду, и сохранить ее чтобы использовать снова и снова по мере того как данные будут меняться (лучше всего сделать это с помощью представления, которое мы будем проходить в Главе 20 ).

РЕЗЮМЕ

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

Следующим шагом будет овладение трем другими специальными операторами которые берут подзапросы как аргументы, это — ANY, ALL, и SOME. Как вы увидите в Главе 13, это — альтернативные формулировки некоторых вещей которые вы уже использовали, но которые в некоторых случаях, могут оказаться более предпочтительными.

Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL