Вложенные и связанные подзапросы в SQL, предикат EXISTS. Операторы EXISTS и NOT EXISTS Pl sql not exists синтаксис

WHERE EXISTS

Подзапрос проверяется на наличие одной или нескольких строк. Если хотя бы одна строка удовлетворяет запросу, то возвращается булево значение ИСТИНА. При указании дополнительного ключевого слова NOT булево значение ИСТИНА возвращается, если подзапрос не возвращает соответствующих ему строк.

подзапрос

На основе полностью сформированного подзапроса извлекается результирующий набор данных.

Общие правила

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

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM employee WHERE jobs.job_id=employye. job_id);

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

SELECT au_lname FROM authors WHERE EXISTS (SELECT * FROM publishers WHERE authors.city=publishers.city);

Этот запрос возвращает фамилии авторов (au_lname), которые живут в том же городе, что и издатели (publishers). Обратите внимание, что можно использовать в подзапросе звездочку, поскольку подзапрос должен вернуть всего лишь одну запись с булевым значением ИСТИНА. В таких случаях столбцы не играют роли. Ключевой момент - это существование строки.

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

Оператор EXISTS семантически эквивалентен оператору ANY.

Подзапрос в операторе EXISTS обычно производит один из двух видов поиска. Первый вариант - это использование группового символа - звездочки (например, SELECT * FROM…), и в этом случае вы не извлекаете какой-то конкретный столбец или значение. Звездочка здесь означает «любой столбец». Второй вариант - выбор в подзапросе только одного конкретного столбца (например, SELECT aujd FROM). Некоторые отдельные платформы позволяют выполнять подзапросы по нескольким столбцам (например, SELECT aujd, aujname FROM…). Однако эта возможность достаточно редкая и ее следует избегать в коде, который нужно переносить на другие платформы.

Различия между платформами

Все платформы поддерживают оператор EXISTS в том виде, который мы описали выше.

Разница между EXISTS и IN в SQL? (14)

В чем разница между предложением EXISTS и IN в SQL?

Когда мы должны использовать EXISTS , и когда мы должны использовать IN ?

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

CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT) GO CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20)) GO INSERT INTO t1 SELECT 1, "title 1", 5 UNION ALL SELECT 2, "title 2", 5 UNION ALL SELECT 3, "title 3", 5 UNION ALL SELECT 4, "title 4", 5 UNION ALL SELECT null, "title 5", 5 UNION ALL SELECT null, "title 6", 5 INSERT INTO t2 SELECT 1, 1, "data 1" UNION ALL SELECT 2, 1, "data 2" UNION ALL SELECT 3, 2, "data 3" UNION ALL SELECT 4, 3, "data 4" UNION ALL SELECT 5, 3, "data 5" UNION ALL SELECT 6, 3, "data 6" UNION ALL SELECT 7, 4, "data 7" UNION ALL SELECT 8, null, "data 8" UNION ALL SELECT 9, 6, "data 9" UNION ALL SELECT 10, 6, "data 10" UNION ALL SELECT 11, 8, "data 11"

Запрос 1

SELECT FROM t1 WHERE not EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)

Запрос 2

SELECT t1.* FROM t1 WHERE t1.id not in (SELECT t2.t1id FROM t2)

Если в t1 ваш идентификатор имеет нулевое значение, Query 1 найдет их, но Query 2 не сможет найти нулевые параметры.

Я имею в виду, что IN не может сравнивать ничего с нулевым, поэтому он не имеет результата для null, но EXISTS может сравнивать все с нулевым.

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

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

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

Вы можете использовать подзапрос, чтобы проверить, существует ли набор записей. Для этого вам нужно использовать предложение exists с подзапросом. Ключевое слово exists всегда возвращает значение true или false.

Какой из них быстрее зависит от количества запросов, полученных внутренним запросом:

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

EXIST оценивает значение true или false, но IN сравнивает множественное значение. Когда вы не знаете, что запись существует или нет, вы должны выбрать EXIST

Ключевое слово Exists оценивает значение true или false, но ключевое слово IN сравнивает все значения в соответствующем столбце подзапроса. Другой Select 1 можно использовать с командой Exists . Пример:

SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...)

Но IN менее эффективен, поэтому Exists быстрее.

На основе оптимизатора правил :

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

На основе оптимизатора затрат :

Нет никакой разницы.

По моим сведениям, когда подзапрос возвращает значение NULL весь оператор становится NULL . В этом случае мы используем ключевое слово EXITS . Если мы хотим сравнить определенные значения в подзапросах, то мы используем ключевое слово IN .

EXISTS - это когда вам нужно сопоставить результаты запроса с другим подзапросом. Результаты запроса # 1 должны быть получены, когда результаты SubQuery совпадают. Вид Присоединиться.. Например, выберите таблицу клиентов №1, которые также разместили таблицу заявок # 2

IN следует извлекать, если значение определенного столбца находится в списке (1,2,3,4,5). Например, выберите клиентов, которые лежат в следующих zipcodes, то есть значения zip_code находятся в списке (....).

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

Я обнаружил, что использование ключевого слова EXISTS часто очень медленное (это очень верно в Microsoft Access). Вместо этого я использую оператор объединения следующим образом:

Я предполагаю, что вы знаете, что они делают, и поэтому используются по-разному, поэтому я буду понимать ваш вопрос следующим образом: Когда было бы хорошей идеей переписать SQL для использования IN вместо EXISTS или наоборот.

Это справедливое предположение?

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

Например:

SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.ID)

можно переписать в:

SELECT * FROM Customers WHERE ID IN (SELECT CustomerID FROM Orders)

или с соединением:

SELECT Customers.* FROM Customers INNER JOIN Orders ON Customers.ID = Orders.CustomerID

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

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

Если вы разработчик MS SQL, вот ответ от Microsoft.

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

Задает подзапрос для проверки существования строк.

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

IN поддерживает только отношения равенства (или неравенства, когда предшествует NOT ).
Это синоним = any / = некоторые , например

Select * from t1 where x in (select x from t2) ;

EXISTS поддерживает варианты типов отношений, которые не могут быть выражены с помощью IN , например -

Select * from t1 where exists (select null from t2 where t2.x=t1.x and t2.y>t1.y and t2.z like "℅" || t1.z || "℅") ;

И на другую ноту -

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

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

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

Синтаксическая форма: 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.CNumFirst.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.CNumT.CNum AND F.SNum=S.SNum) – возвращает номера, имена и города проживания всех продавцов, обслуживших нескольких покупателей.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Предикаты количественного сравнения

Синтаксическая форма: {=|>|=|} 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 Amt ALL(SELECT Rating FROM Customer WHERE City=’Rome’)

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

UNIQUE|DISTINCT ()

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

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

MATCH ()

Предикат 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 …

В этом учебном материале вы узнаете, как использовать SQL условие EXISTS с синтаксисом и примерами.

Описание

SQL условие EXISTS используется в сочетании с подзапросом и считается выполненным, если подзапрос возвращает хотя бы одну строку. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис условия EXISTS в SQL:

Параметры или аргументы

subquery подзапрос является оператором SELECT. Если subquery возвращает хотя бы одну запись в своем наборе результатов, предложение EXISTS оценивается как true и условие EXISTS будет выполнено. Если subquery не возвращает никаких записей, предложение EXISTS оценивается как false, и условие EXISTS не будет выполнено.

Примечание

Операторы SQL, использующие условие EXISTS, очень неэффективны, поскольку подзапрос повторно запускается для КАЖДОЙ строки в таблице внешнего запроса. Есть более эффективные способы написания большинства запросов, которые не используют условие EXISTS.

Пример — использование условия EXISTS с оператором SELECT

Давайте начнем с примера, который показывает, как использовать условие EXISTS с оператором SELECT.

В этом примере у нас есть таблица customers со следующими данными:

Теперь давайте найдем все записи из таблицы customers , где есть хотя бы одна запись в таблице orders с тем же customer_id . Выполните следующий SELECT запрос:

Будет выбрано 4 записи. Вот результаты, которые вы должны получить:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL

В этом примере в таблице customers есть 4 записи, у которых значение customer_id имеется в таблице orders .

Пример — использование условия EXISTS с оператором UPDATE

Давайте рассмотрим пример, в котором используется условие EXISTS в операторе UPDATE.
В этом примере у нас есть таблица products со следующими данными:

Теперь давайте обновим таблицу summary_data значениями из таблицы products . Введите следующий SQL оператор:

PgSQL

Будет обновлено 5 записей. Снова выберите данные из таблицы summary_data :

PgSQL

SELECT * FROM summary_data;

В этом примере будет обновлено поле current_category в таблице summary_data данными category_id из таблицы products , где значения product_id совпадают. Первые 5 записей в таблице summary_data были обновлены.

Подсказка : Если бы мы не включили условие EXISTS, запрос UPDATE обновил бы поле current_category на NULL в 6-й строке таблицы summary_data (поскольку таблица products не имеет записи, где product_id = 8).