Выполнение сложных SQL-запросов. Выполнение сложных SQL-запросов Параметры или аргументы
Оператор INTERSECT извлекает идентичные строки из результирующих наборов одного или нескольких запросов. В некотором отношении оператор INTERSECT очень напоминает INNER JOIN.
INTERSECT относится к классу операторов для работы с наборами данных (set operator). К другим таким операторам относятся EXCEPT и UNION. Все операторы для наборов данных используются для одновременного манипулирования результирующими наборами двух и более запросов, отсюда и их название.
Синтаксис SQL2003
Технических ограничений на количество запросов в операторе INTERSECT не существует. Общий синтаксис следующий.
INTERSECT
] INTERSECT
Ключевые слова
Включаются дублирующиеся строки из всех результирующих наборов.
DISTINCT
Дублирующиеся строки удаляются из всех результирующих наборов перед сравнением, проводимым оператором INTERSECT. Столбцы с пустыми (NULL) значениями считаются дублирующимися. Если не указано ни ключевое слово ALL, ни DISTINCT, то по умолчанию подразумевается DISTINCT.
CORRESPONDING
Указывается, что возвращаться будут только те столбцы, которые имеют в обоих запросах одно имя, даже если в обоих запросах используется обобщающий символ (*).
Указывается, что возвращаться будут только названные столбцы, даже если запросы обнаруживают другие столбцы с соответствующими именами. Это предложение должно использоваться вместе с ключевым словом CORRESPONDING.
Общие правила
Есть только одно важное правило, которое необходимо помнить при работе с оператором INTERSECT.
Порядок и количество столбцов во всех запросах должно быть одинаково. Типы данных соответствующих столбцов также должны быть совместимы.
Например, типы CHAR и VARCHAR совместимы. По умолчанию для результирующего набора в каждом столбце будет использоваться размер, соответствующий самому большому типу в каждом конкретном положении.
Ни одна из платформ не поддерживает предложение CORRESPONDING .
Согласно стандарту ANSI оператор INTERSECT имеет более высокий приоритет по сравнению с другими операторами для работы с наборами, хотя на разных платформах приоритет таких операторов обрабатывается по-разному. Вы можете явным образом управлять приоритетом операторов, используя скобки. В противном случае СУБД может выполнять их в порядке слева направо или от первого к последнему.
Согласно стандарту ANSI в запросе можно использовать только одно предложение ORDER BY. Вставляйте его в самый конец последней инструкции SELECT. Чтобы избежать двусмысленности в указании столбцов и таблиц, обязательно присваивайте один и тот же псевдоним всем соответствующим друг другу столбцам таблиц. Например:
На платформах, которые не поддерживают оператор INTERSECT, вы можете заменить его подзапросом FULL JOIN.
SELECT a.au_lname AS "lastname", a.au_fname AS "firstname" FROM authors AS a INTERSECT SELECT e.emp_lname AS "lastname", e.emp_fname AS "firstname" FROM employees AS e ORDER BY lastname, firstname;
Поскольку типы данных столбцов в разных запросах оператора INTERSECT могут оказываться совместимыми, на разных платформах СУРБД могут встречаться разные варианты работы со столбцами разной длины. Например, если столбец aujname из первого запроса в предыдущем примере значительно длиннее, чем столбец empjname из второго запроса, то разные платформы могут применять разные правила определения длины конечного результата. Но, вообще говоря, платформы будут выбирать для результата более длинный (и менее ограниченный) размер.
Каждая СУРБД может применять свои собственные правила использования имени столбца в том случае, если имена в списках столбцов различаются. Обычно используются имена столбцов первого запроса.
DB2
Платформа DB2 поддерживает ключевые слова INTERSECT и INTERSECT ALL стандарта ANSI плюс дополнительное предложение VALUES.
{инструкция._SELECT_7 | VALUES (выраж7 [, …])} INTERSECT
] {инструкция_SCJ_2 | VALUES {выраж2 [, …])} INTERSECT
Хотя инструкция INTERSECT DISTINCT не поддерживается, функциональным эквивалентом является INTERSECT. Предложение CORRESPONDING не поддерживается.
Кроме того, типы данных LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK и структурные типы не применяются в предложении INTERSECT, но их можно использовать в предложении INTERSECT ALL.
Если в результирующем наборе данных есть столбец, имеющий одно и то же имя во всех инструкциях SELECT, то это имя используется в качестве окончательного имени для столбца, возвращаемого инструкцией. Если же в запросах для столбца используются разные имена, то платформа DB2 сгенерирует для результирующего столбца новое имя. После этого становится непригодным для использования в предложениях ORDER BY и FOR UPDATE.
Если в одном запросе используется несколько операторов для работы с наборами данных, то первым выполняется тот, который заключен в скобки. После этого порядок выполнения будет слева направо. Тем не менее все операторы INTERSECT выполняются до операторов UNION и EXCEPT, например:
SELECT empno FROM employee WHERE workdept LIKE "E%" INTERSECT (SELECT empno FROM emp_act WHERE projno IN ("IF1000", "IF2000", "AD3110") UNION VALUES ("AA0001"), ("AB0002"), ("AC0003"))
В приведенном выше примере из таблицы employee извлекаются идентификаторы (ID) всех служащих, работающих в департаменте, название которого начинается с «Е». Однако идентификаторы извлекаются только в том случае, если они также существуют в таблице учетных записей служащих с именем emp_act и участвуют в проектах IF1000, IF200" и AD3110.
This SQL tutorial explains how to use the SQL INTERSECT operator with syntax and examples.
Description
The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Intersect Query
Explanation: The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2.
Each SQL statement within the SQL INTERSECT must have the same number of fields in the result sets with similar data types.
Syntax
The syntax for the INTERSECT operator in SQL is:
SELECT expression1, expression2, ... expression_n FROM tables INTERSECT SELECT expression1, expression2, ... expression_n FROM tables ;
Parameters or Arguments
expression1, expression2, expression_n The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected.Example - With Single Expression
The following is a SQL INTERSECT operator example that has one field with the same data type:
SELECT supplier_id FROM suppliers INTERSECT SELECT supplier_id FROM orders;
In this SQL INTERSECT example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.
Now, let"s complicate our example further by adding to the INTERSECT query.
SELECT supplier_id FROM suppliers WHERE supplier_id > 78 INTERSECT SELECT supplier_id FROM orders WHERE quantity <> 0;
In this example, the WHERE clauses have been added to each of the datasets. The first dataset has been filtered so that only records from the suppliers table where the supplier_id is greater than 78 are returned. The second dataset has been filtered so that only records from the orders quantity is not equal to 0.
Example - With Multiple Expressions
Next, let"s look at an example of how to use the INTERSECT operator in SQL to return more than one column.
SELECT contact_id, last_name, first_name FROM contacts WHERE last_name <> "Anderson" INTERSECT SELECT customer_id, last_name, first_name FROM customers WHERE customer_id < 50;
In this INTERSECT example, the query will return the records from the contacts table where the contact_id , last_name , and first_name values match the customer_id , last_name , and first_name value from the customers table.
There are WHERE conditions on each data set to further filter the results so that only records from the contacts are returned where the last_name is not Anderson . The records from the customers table are returned where the customer_id is less than 50.
Example - Using ORDER BY
SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id > 2000 INTERSECT SELECT company_id, company_name FROM companies WHERE company_id > 1000 ORDER BY 2;Since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we"ve sorted the results by supplier_name / company_name in ascending order, as denoted by the ORDER BY 2 .
The supplier_name / company_name fields are in position #2 in the result set.
SQL предоставляет два способа объединения таблиц:- указывая соединяемые таблицы (в том числе подзапросы) во фразе FROM оператора SELECT . Сначала выполняется соединение таблиц, а уже потом к полученному множеству применяются указанные фразой WHERE условия, определяемое фразой GROUP BY агрегирование, упорядочивание данных и т.п.;
- определяя объединение результирующих наборов, полученных при обработке оператора SELECT . В этом случае два оператора SELECT соединяются фразой UNION , INTERSECT , EXCEPT или CORRESPONDING .
UNION-объединение
Фраза UNION объединяет результаты двух запросов по следующим правилам:
Стандарт не накладывает никаких ограничений на упорядочивание строк в результирующем наборе. Так, некоторые СУБД сначала выводят результат первого запроса, а затем - результат второго запроса. СУБД Oracle автоматически сортирует записи по первому указанному столбцу даже в том случае, если для него не создан индекс.
Для того чтобы явно указать требуемый порядок сортировки, следует использовать фразу ORDER BY . При этом можно использовать как имя столбца, так и его номер (рис. 4.3).
Рис. 4.3.
Фраза UNION ALL выполняет объединение двух подзапросов аналогично фразе UNION со следующими исключениями:
- совпадающие строки не удаляются из формируемого результирующего набора;
- объединяемые запросы выводятся в результирующем наборе последовательно без упорядочивания.
При объединении более двух запросов для изменения порядка выполнения операции объединения можно использовать скобки (рис. 4.4).
Рис. 4.4.
INTERSECT-объединение
Фраза INTERSECT позволяет выбрать только те строки, которые присутствуют в каждом объединяемом результирующем наборе. На рис. 4.5 приведен пример объединения запросов как пересекающихся множеств.
Рис. 4.5.
EXCEPT-объединение
Фраза EXCEPT позволяет выбрать только те строки, которые присутствуют в первом объединяемом результирующем наборе, но отсутствуют во втором результирующем наборе.
Фразы INTERSECT и EXCEPT должны поддерживаться только при полном уровне соответствия стандарту SQL-92. Так, некоторые СУБД вместо фразы
На уроке будет рассмотрена тема использования операций объединения, пересечения и разности запросов. Разобраны примеры того, как используется SQL запрос Union, Exists, а также использование ключевых слов SOME, ANY и All. Рассмотрены строковые функции
Над множеством можно выполнять операции объединения, разности и декартова произведения. Те же операции можно использовать и в sql запросах (выполнять операции с запросами).
Для объединения нескольких запросов используется служебное слово UNION .
Синтаксис:
< запрос 1 > UNION [ ALL ] < запрос 2 > <запрос 1> UNION <запрос 2>
SQL запрос Union служит для объединения выходных строк каждого запроса в один результирующий набор.
Если используется параметр ALL , то сохраняются все дубликаты выходных строк. Если параметр отсутствует, то в результирующем наборе остаются только уникальные строки.
Объединять вместе можно любое число запросов.
Использование оператора UNION требует выполнения нескольких условий:
- количество выходных столбцов каждого из запросов должно быть одинаковым;
- выходные столбцы каждого из запросов должны быть сравнимы между собой по типам данных (в порядке их очередности);
- в итоговом наборе используются имена столбцов, заданные в первом запросе;
- ORDER BY может быть использовано только в конце составного запроса, так как оно применяетя к результату объединения.
Пример: Вывести цены на компьютеры и ноутбуки, а также их номера (т.е. произвести выгрузку из двух разных таблиц в одном запросе)
✍ Решение:
| 1 2 3 4 5 6 | SELECT `Номер` , `Цена` FROM pc UNION SELECT `Номер` , `Цена` FROM notebook ORDER BY `Цена` |
SELECT `Номер` , `Цена` FROM pc UNION SELECT `Номер` , `Цена` FROM notebook ORDER BY `Цена`
Результат:
Рассмотрим более сложный пример с объединением inner join:
Пример: Найти тип продукции, номер и цену компьютеров и ноутбуков
✍ Решение:
| 1 2 3 4 5 6 7 8 | SELECT product. `Тип` , pc. `Номер` , `Цена` FROM pc INNER JOIN product ON pc. `Номер` = product. `Номер` UNION SELECT product. `Тип` , notebook. `Номер` , `Цена` FROM notebook INNER JOIN product ON notebook. `Номер` = product. `Номер` ORDER BY `Цена` |
SELECT product.`Тип` , pc.`Номер` , `Цена` FROM pc INNER JOIN product ON pc.`Номер` = product.`Номер` UNION SELECT product.`Тип` , notebook.`Номер` , `Цена` FROM notebook INNER JOIN product ON notebook.`Номер` = product.`Номер` ORDER BY `Цена`
Результат:
SQL Union 1. Найти производителя, номер и цену всех ноутбуков и принтеров
SQL Union 2. Найти номера и цены всех продуктов, выпущенных производителем Россия
SQL Предикат существования EXISTS
В языке SQL есть средства для выполнения операций пересечения и разности запросов — предложение INTERSECT (пересечение) и предложение EXCEPT (разность). Эти предложения работают подобно тому, как работает UNION: в результирующий набор попадают только те строки, которые присутствуют в обоих запросах — INTERSECT , или только те строки первого запроса, которые отсутствуют во втором — EXCEPT . Но беда в том, что многие СУБД не поддерживают эти предложения. Но выход есть — использование предиката EXISTS .
Предикат EXISTS принимает значение TRUE (истина), если подзапрос возвращает хоть какое-нибудь количество строк, иначе EXISTS принимает значение FALSE. Существует также предикат NOT EXISTS, который действует противоположным образом.
Обычно EXISTS используется в зависимых подзапросах (например, IN).
EXISTS(табличный подзапрос)
Пример: Найти тех производителей компьютеров, которые производят также и ноутбуки
✍ Решение:
SELECT DISTINCT Производитель FROM product AS pc_product WHERE Тип = "Компьютер" AND EXISTS (SELECT Производитель FROM product WHERE Тип = "Ноутбук" AND Производитель = pc_product.Производитель)
Результат:
Найти тех производителей компьютеров, которые не производят принтеров
Ключевые слова SQL SOME | ANY и ALL
Ключевые слова SOME и ANY являются синонимами, поэтому в запросе можно использовать любое из них. Результатом такого запроса будет являться один столбец величин.
Синтаксис:
< выражение>< оператор сравнения> SOME | ANY (< подзапрос> ) <выражение><оператор сравнения>SOME | ANY (<подзапрос>)
Если для какого-нибудь значения X , получаемого из подзапроса, результат операции « » возвращает TRUE , то предикат ANY также равняется TRUE .
Пример: Найти поставщиков компьютеров, у которых номера отсутствуют в продаже (т.е. отсутствуют в таблице pc)
✍ Решение:
Исходные данные таблиц:
Результат:
В примере предикат Номер = ANY(SELECT Номер FROM pc) вернет в том случае значение TRUE, когда Номер из основного запроса найдется в списке Номеров таблицы pc (возвращаемом подзапросом). Кроме того, используется NOT . Результирующий набор будет состоять из одного столбца — Производитель. Чтобы один производитель не выводился несколько раз, введено служебное слово DISTINCT .
Теперь рассмотрим использование ключевого слова ALL:
Пример: Найти номера и цены ноутбуков, стоимость которых превышает стоимость любого компьютера
✍ Решение:
Важно: Стоит заметить, что в общем случае запрос с ANY возвращает множество значений. Поэтому использование подзапроса в предложении WHERE без операторов EXISTS , IN , ALL и ANY , которые дают булево значение (логическое), может привести к ошибке времени выполнения запроса
Пример: Найти номера и цены компьютеров, стоимость которых превышает минимальную стоимость ноутбуков
✍ Решение:
Этот запрос корректен по той причине, что скалярное выражение Цена сравнивается с подзапросом, который возвращает единственное значение
Функции работы со строками в SQL
Функция LEFT вырезает слева из строки заданное вторым аргументом число символов:
LEFT (<строка>,<число>)
Функция RIGHT возвращает заданное число символов справа из строкового выражения:
RIGHT(<строка>,<число>)
Пример: Вывести первые буквы из названий всех производителей
✍ Решение:
SELECT DISTINCT LEFT(`Производитель` , 1) FROM `product`
Результат:
Пример: Вывести названия производителей, которые начинаются и заканчиваются на одну и ту же букву
✍ Решение:
Функция SQL Replace
Синтаксис:
SELECT `name` , REPLACE(`name` , "а", "аа") FROM `teachers`