Как понять логику EXISTS в SQL запросах

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

Начнем с того, что EXISTS является предикатом. В общем смысле, предикат, это утверждение, высказанное о субъекте. В программировании, это функции, которые определяют истинность выражения, при разных значениях, и возвращают результат логического типа: true, либо false. 

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

Например,

SELECT ProductName 
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

Вряд ли у кого-то вызовет недоумение, такая структура запроса. 

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

SELECT SupplierName
FROM Suppliers
WHERE SupplierID IN (SELECT SupplierID 
                     FROM Products WHERE Products.SupplierID = Suppliers.supplierID 
                     AND Price < 20);

Но как только появляется EXISTS, как нить понимания взаимосвязи внешнего и вложенного запроса, начинает порой теряться.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products 
              WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Для примеров и выполнения запросов, будет использоваться база, предоставленная на площадке w3schools, нажав, в глоссарии, на любую из кнопок 'Try it yourself', вы попадете в редактор запросов.

А тем, кто хочет потренироваться в решении SQL задач, до встречи в ТГ канале SQL решает.

Если погуглить, стандартное конспективное описание работы оператора EXISTS, для SQL, сводится обычно к следующему: “Оператор EXISTS возвращает true, если подзапрос возвращает одну, или более записей, в противном случае, возвращает false”. 

И еще: “Поскольку возвращения набора строк не происходит, то подзапросы с EXISTS оператором выполняются довольно быстро.”

Так вот, непонимание, обычно, как раз кроется, где-то здесь: Если EXISTS возвращает true/false, но не возвращает набор записей, то каким образом, основной запрос, в ходе выполнения, отбирает записи, соответствующие условиям описанным во вложенном запросе?

Для иллюстрации работы EXISTS на практике, рассмотрим следующий пример:

SELECT 
EXISTS(SELECT * FROM Products WHERE ProductName = "Mozzarella") AS Mozzarella,
EXISTS(SELECT * FROM Products WHERE ProductName = "Tofu") AS Tofu;

Результат

Несложно догадаться, что если записи о соответствующем продукте в базе отсутствуют, то вернется 0 (false), если же вложенный запрос нашел одну, или более записей, то вернется 1 (true).

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

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products 
              WHERE Products.SupplierID = Suppliers.supplierID 
              AND Price < 20);

Из описания помним, что EXISTS, возвращает только true, либо false, но набор записей, при этом, не возвращается. 

Так каким же образом, внешний (outer) запрос согласуется с вложенным?

Ведь если мы напишем что-то вроде:

SELECT * FROM Suppliers
WHERE True;

То у нас просто выведется на экран все содержимое таблицы Suppliers, без какой бы то ни было условной выборки... А если true, заменим на false, то не выведется ни одной записи.

Ну ок, снова вернемся к нашему примеру: 

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products 
              WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Для тех, кто не в курсе, уточним, здесь вложенный запрос является коррелированным. То есть, вложенный запрос, ссылается на данные из внешнего запроса. Иначе говоря, присутствует корреляция, между внешним запросом, и вложенным. Эта связь, между запросами реализуется в строке: Products.SupplierID = Suppliers.supplierID. 

При этом, вложенный запрос, передается, как аргумент в функцию EXISTS(<вложенный_запрос>), которая, по результату выполнения вложенного запроса, может вернуть только бинарное значение true, либо false. 

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

То есть, если вложенный запрос найдет записи подходящие под условия, на экране отобразится список записей из таблицы ‘Suppliers’, при чем только лишь тех записей, которые матчатся по айдишникам с таблицей ‘Products’, плюс, для которых справедливо условие: Price < 20. 

Ну хорошо, допустим, нашел наш вложенный запрос некое множество строк, но почему мы видим на экране, отфильтрованную выборку - то есть только те записи, которые соответствуют условию, прописанному во вложенном запросе, если на выходе, выхлопом от EXISTS, может быть только 1, либо 0 ? 

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

… WHERE Products.SupplierID = 1)  AND Price < 20);;
… WHERE Products.SupplierID = 2)  AND Price < 20);
… WHERE Products.SupplierID = 3)  AND Price < 20);

где 1, 2 и 3, это айдишники из внешней таблицы (Suppliers) из внешнего запроса.

Но чем это принципиально отличается от: 

SELECT * FROM Suppliers
WHERE True;

логика работы EXISTS ведь никак не поменялась. 

А дело тут вот в чем … 

Как гласит документация, работа с запросами в MySQL идет “снаружи” “вовнутрь” (MySQL evaluates queries “from outside to inside). 

То есть, сначала будут выполнены инструкции внешнего запроса:

SELECT SupplierID, SupplierName
FROM Suppliers

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

NOTE: Столбец SupplierID добавлен в блок SELECT, исключительно для наглядности.

Теперь, вложенный запрос выполнится для первой строки таблицы из внешнего запроса:

SELECT SupplierID, SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products 
              WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

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

SELECT SupplierID, SupplierName
FROM Suppliers
WHERE EXISTS (SELECT * FROM Products WHERE Products.SupplierID = 1 AND Price < 20);

Допустим, вложенный запрос, нашел соответствующие записи. При этом, как мы помним из описания работы EXISTS, он возвращает только true/false, но сам набор найденных строк не возвращается. 

Так и есть. Если вложенный запрос, нашел записи отвечающие условиям, EXISTS возвращает true, и это означает, что соответствующая запись, из таблицы внешнего запроса попадет в результирующую выборку (result set), которую мы и увидим на экране, как итог выполнения запроса. 

В словесной форме, данную логику можно было бы описать примерно так:

• Выводить ли нам на экран первую запись из таблицы внешнего запроса? (Да/Нет)

• Если вложенный запрос вернет одну, или более записей (EXISTS()==true), то да, если ничего не найдет (EXISTS()==false), то нет. 
  Допустим, вложенный запрос нашел соответствия (true), значит на экран будет выведена текущая запись из таблицы основного (внешнего) запроса.

• Ок, выводить ли нам на экран вторую запись из таблицы внешнего запроса?  (Да/Нет)

• Вложенный запрос, записей соответствующих условиям, не обнаружил (false), запись с SupplierID=2, из таблицы внешнего запроса (Suppliers), в результирующую выборку, не попадает. И т.д..

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

Получается, что нам не нужно, чтобы вложенный запрос, возвращал нам набор строк. Все, что нам необходимо, это получить ответ, исходя из условия описанного во вложенном запросе, будем ли мы выводить на экран запись из таблицы внешнего запроса, Да (True), или Нет (False).

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

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

Если EXISTS используется не в тандеме с коррелированным подзапросом, то в случае TRUE, все записи основного запроса будут: выведены на экран / обновлены / удалены, в зависимости от того, какой запрос используется.

Например,

UPDATE Categories SET Description = "Cheeses and sousages" 
WHERE EXISTS (
SELECT * FROM Categories WHERE Description LIKE "%Cheeses%");

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

Чтобы изменения коснулись лишь одной записи, нам придется переписать пример выше, и сделать подзапрос коррелированным:

UPDATE Categories as c SET Description = "Cheeses and sousages" WHERE EXISTS (
SELECT * FROM Categories 
  WHERE Description LIKE "%Cheeses%" AND Categories.CategoryID=c.CategoryID);

Тогда изменения будут применены, только к полю необходимой записи:

Ну и поскольку конструкция NOT EXISTS, также существует, то напоследок небольшая памятка о том, какое значение будет возвращено в каждом случае:

• If EXISTS (subquery) returns at least 1 row, the result is TRUE.

• If EXISTS (subquery) returns no rows, the result is FALSE.

• If NOT EXISTS (subquery) returns at least 1 row, the result is FALSE.

• If NOT EXISTS (subquery) returns no rows, the result is TRUE.

Успехов в обучении!

P.S. Напомню, для кого актуально размять мозги, потренироваться в написании SQL запросов, и решении задач, а также прокачаться перед интервью, добро пожаловать в ТГ канал SQL решает