SQL: EXISTS Condition
This SQL tutorial explains how to use the SQL EXISTS condition with syntax and examples.
Description
The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the EXISTS condition in SQL is:
Parameters or Arguments
- SQL statements that use the EXISTS condition are very inefficient since the sub-query is rerun for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do not use the EXISTS condition.
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Example — Using EXISTS Condition with the SELECT Statement
Let’s start by looking at an example that shows how to use the EXISTS condition with a SELECT statement.
In this example, we have a customers table with the following data:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 5000 | Smith | Jane | digminecraft.com |
| 6000 | Ferguson | Samantha | bigactivities.com |
| 7000 | Reynolds | Allen | checkyourmath.com |
| 8000 | Anderson | Paige | NULL |
| 9000 | Johnson | Derek | techonthenet.com |
And a table called orders with the following data:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2016/04/18 |
| 2 | 5000 | 2016/04/18 |
| 3 | 8000 | 2016/04/19 |
| 4 | 4000 | 2016/04/20 |
Now let’s find all of the records from the customers table where there is at least one record in the orders table with the same customer_id. Enter the following SELECT statement:
There will be 4 records selected. These are the results that you should see:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 5000 | Smith | Jane | digminecraft.com |
| 7000 | Reynolds | Allen | checkyourmath.com |
| 8000 | Anderson | Paige | NULL |
In this example, there are 4 records in the customers where the customer_id value appears in the orders table.
Example — Using EXISTS Condition with the UPDATE Statement
Let’s look at an example that uses the EXISTS condition in an UPDATE statement.
In this example, we have a table called products with the following data:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
And a table called summary_data with the following data:
| product_id | current_category |
|---|---|
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
Now let’s update the summary_data table with values from the products table. Enter the following SQL statement:
There will be 5 records update. Select the data from the summary_data table again:
These are the results that you should see:
| product_id | current_category |
|---|---|
| 1 | 50 |
| 2 | 50 |
| 3 | 50 |
| 4 | 50 |
| 5 | 75 |
| 8 | 10 |
This example would update the current_category field in the summary_data table with the category_id from the products table where the product_id values match. The first 5 records in the summary_data table have been updated.
TIP: If we hadn’t included the EXISTS condition, the UPDATE query would have updated the current_category field to NULL in the 6th row of the summary_data table (because the products table does not have a record where product_id=8).
Example — Using EXISTS Condition with the DELETE Statement
Let’s look at an example that uses the EXISTS condition in a DELETE statement.
In this example, we have a table called customers with the following data:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 5000 | Smith | Jane | digminecraft.com |
| 6000 | Ferguson | Samantha | bigactivities.com |
| 7000 | Reynolds | Allen | checkyourmath.com |
| 8000 | Anderson | Paige | NULL |
| 9000 | Johnson | Derek | techonthenet.com |
And a table called orders with the following data:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2016/04/18 |
| 2 | 5000 | 2016/04/18 |
| 3 | 8000 | 2016/04/19 |
| 4 | 4000 | 2016/04/20 |
| 5 | NULL | 2016/05/01 |
Enter the following DELETE statement:
There will be 1 record deleted. Select the data from the orders table again:
These are the results that you should see:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2016/04/18 |
| 2 | 5000 | 2016/04/18 |
| 3 | 8000 | 2016/04/19 |
| 5 | NULL | 2016/05/01 |
This example would delete all records from the orders table where there is a record in the customers table with the last_name of ‘Jackson’ and a matching customer_id value in both tables. In this example, the record for order_id=4 was deleted.
If you want to determine the number of rows that will be deleted, you can run the following SELECT statement before performing the delete.
This will return number of records that will be deleted when you execute the DELETE statement.
| COUNT(*) |
|---|
| 1 |
Example — Using NOT with the EXISTS Condition
Finally, the NOT condition can be combined with the EXISTS condition to create a NOT EXISTS condition. Let’s look at an example that shows how to use the NOT EXISTS condition in SQL.
In this example, we have a table called customers with the following data:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 4000 | Jackson | Joe | techonthenet.com |
| 5000 | Smith | Jane | digminecraft.com |
| 6000 | Ferguson | Samantha | bigactivities.com |
| 7000 | Reynolds | Allen | checkyourmath.com |
| 8000 | Anderson | Paige | NULL |
| 9000 | Johnson | Derek | techonthenet.com |
And a table called orders with the following data:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2016/04/18 |
| 2 | 5000 | 2016/04/18 |
| 3 | 8000 | 2016/04/19 |
| 4 | 4000 | 2016/04/20 |
| 5 | NULL | 2016/05/01 |
Enter the following SQL statement:
There will be 2 records selected. These are the results that you should see:
| customer_id | last_name | first_name | favorite_website |
|---|---|---|---|
| 6000 | Ferguson | Samantha | bigactivities.com |
| 9000 | Johnson | Derek | techonthenet.com |
This example would return all records from the customers table where there are no records in the orders table for the given customer_id.
SQL-операторы: руководство с примерами запросов. Часть 2
Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».
В первой части статьи мы рассмотрели такие темы:
В этой части мы рассмотрим:
Операторы для проверки существования (IN / NOT IN)
Если мы хотим проверить, есть ли определенное значение в списке значений, мы можем воспользоваться операторами IN или NOT IN :
Аналогично, для отрицания используется NOT IN :
Частичное совпадение — использование LIKE
Иногда нам нужно найти строки, основываясь на частичном совпадении.
Допустим, мы хотим найти всех пользователей, которые зарегистрировались в нашем приложении при помощи адреса Gmail. Мы можем поискать частичное совпадение в столбце, используя ключевое слово LIKE . Также при этом можно использовать групповой символ — % .
Поиск пользователей, чей email оканчивается на gmail.com :
Строка %gmail.com означает «совпадение со всем, что кончается на gmail.com».
Если мы посмотрим на данные наших пользователей, мы заметим, что среди них только у двоих адрес электронной почты кончается на gmail.com :
Но в email Джейн указана заглавная «G». Предыдущий запрос не выберет эту запись, потому что мы ищем точное совпадение с gmail.com, а там «g» в нижнем регистре.
Чтобы поиск не зависел от регистра, нужно заменить LIKE на ILIKE :
Групповой символ % в начале строки означает, что вернуть нужно все, что заканчивается на «gmail.com». Это может быть и ob.jones+12345@gmail.com, и asdflkasdflkj@gmail.com — главное, чтобы в конце стояло «gmail.com».
Мы также можем использовать столько групповых символов, сколько нам нужно.
Например, поиск %j%o% вернет любой email-адрес, соответствующий шаблону «<все-что-угодно>, за чем следует j, за чем следует <все-что-угодно>, за чем следует o, за чем следует <все-что-угодно>»:
Работа с отсутствующими данными (NULL)
Давайте посмотрим, как быть со столбцами и строками, где нет данных.
Для этого давайте добавим в нашу таблицу users еще один столбец: first_paid_at .
Этот новый столбец будет TIMESTAMP (подобно datetime в других языках) и будет представлять дату и время, когда пользователь впервые заплатил нам за наше приложение. Может, мы хотим послать ему открытку и цветы в честь годовщины.
Мы могли бы стереть нашу таблицу users , введя DROP TABLE users , и пересоздать ее заново, но таким образом мы удалили бы все данные в таблице.
Чтобы изменить таблицу, не стирая ее и не лишаясь данных, можно использовать ALTER TABLE :
Эта команда возвращает результат ALTER TABLE , так что наш запрос ALTER сработал успешно.
Если мы теперь запросим нашу таблицу users , мы заметим, что теперь в ней появился новый столбец без данных:
Наш столбец first_paid_at пуст, и результат нашего psql-запроса показывает, что это пустой столбец. Технически он не пустой: в нем содержится специальное значение, которое psql просто не показывает в выводе — NULL .
NULL это специальное значение в базах данных. Это отсутствие значения, и оно ведет себя не так, как можно было бы ожидать.
Чтобы это продемонстрировать, давайте посмотрим на простой SELECT :
Здесь мы просто выбрали 1 = 1 и 1 = 2 . Как мы и ожидали, результат этих двух предложений — t и f (или TRUE и FALSE ). 1 равен 1, но 1 не равен 2.
Теперь давайте попробуем проделать то же самое с NULL :
Мы могли ожидать, что значением будет FALSE , но на деле возвращается значение NULL .
Чтобы еще лучше визуализировать NULL , давайте при помощи опции \pset посмотрим, как psql отображает NULL-значения:
Если мы запустим этот запрос еще раз, мы увидим в выводе ожидаемый нами NULL :
Итак, 1 не равен NULL , а как насчет NULL = NULL ?
Довольно странно, однако NULL не равен NULL .
NULL лучше представлять себе как неизвестное значение. Равно ли неизвестное значение единице? Мы не знаем, оно же неизвестное. Равно ли неизвестное значение неизвестному значению? Опять же, мы этого не знаем. Это немного лучше поясняет, что такое NULL .
Использование IS NULL и IS NOT NULL
Мы не можем использовать с NULL оператор равенства, но мы можем пользоваться двумя специально созданными для этого операторами: IS NULL и IS NOT NULL .
Эти значения ожидаемы: NULL IS NULL — истина, NULL IS NOT NULL — ложь.
Это все прекрасно и очень интересно, но как это применять на практике?
Что ж, для начала давайте заведем какие-то данные в нашем столбце first_paid_at :
В приведенной выше инструкции UPDATE мы задали значения для столбца first_paid_at у троих разных пользователей: пользователю с ID 1 — текущее время ( NOW() ), пользователю с ID 2 — текущее время минус месяц, а пользователю с ID 3 — текущее время минус год.
Во-первых, давайте найдем пользователей, которые нам уже платили, и пользователей, которые пока этого не делали:
Операторы сравнения при работе с датами и временем
Теперь, когда у нас есть кое-какие данные, давайте используем те же операторы сравнения применительно к новому полю TIMESTAMP .
Попробуем найти пользователей, которые совершили платеж на протяжении последней недели. Для этого мы можем взять текущее время ( NOW() ) и вычесть из него одну неделю при помощи ключевого слова INTERVAL :
Мы также можем использовать другой интервал, например, последние три месяца:
Теперь давайте найдем пользователей, которые совершали платеж в промежутке от одного до шести месяцев назад.
Мы можем скомбинировать наши условия, используя AND , но вместо использования операторов < и > давайте используем ключевое слово BETWEEN :
Проверка существования с использованием EXISTS / NOT EXISTS
Другой способ проверить существование (наличие) значения — использовать EXISTS и NOT EXISTS .
Эти операторы фильтруют строки, проверяя существование или несуществование условия. Это условие обычно является запросом к другой таблице.
Чтобы это продемонстрировать, давайте создадим новую таблицу под названием posts . В этой таблице будут содержаться посты, котоыре пользователь может делать в нашей системе.
Это простая таблица. Она содержит только ID, поле для хранения текста поста ( body ) и ссылку на пользователя, который написал этот пост ( user_id ).
Давайте добавим в новую таблицу некоторые данные:
Согласно добавленным данными, у пользователя с ID 1 есть два поста, у пользователя с ID 2 — один пост, у пользователя с ID 3 — тоже один пост.
Чтобы найти пользователей, у которых есть посты, мы можем использовать ключевое слово EXISTS .
EXISTS принимает подзапрос. Если этот подзапрос возвращает что-либо (даже строку со значением NULL ), база данных включит эту строку в результат.
EXISTS проверяет лишь существование строки из подзапроса, ему не важно, что именно содержится в этой строке.
Вот пример выборки пользователей, имеющих посты:
Как и ождилалось, мы получили пользователей с ID 1, 2 и 3.
Наш подзапрос EXISTS проверяет записи в таблице posts, где user_id поста совпадает со столбцом id таблицы users. Мы вернули 1 в нашем SELECT , потому что здесь мы можем вернуть что угодно: база данных просто хочет видеть, что что-то вернулось.
Аналогично, мы можем найти пользователей, у которых нет постов. Для этого нужно заменить EXISTS на NOT EXISTS :
Наконец, мы можем переписать наш запрос и использовать IN или NOT IN вместо EXISTS или NOT EXISTS :
Технически это сработает, но вообще, если вы проверяете существование другое записи, более производительно будет использовать EXISTS . Операторы IN и NOT IN в целом лучше применять для проверки значения в статическом списке, как мы делали ранее:
Поразрядные операторы
Хотя на практике поразрядные операторы используются нечасто, для полноты картины давайте рассмотрим простой пример.
Если мы по какой-то причине хотим посмотреть возраст наших пользователей в бинарном виде и поиграться с перестановкой битов, мы можем использовать поразрядные операторы.
В качестве примера давайте рассмотрим поразрядный оператор «and»: & .
Чтобы осуществить поразрядную операцию, нам сначала нужно преобразовать значения в нашем столбце age из целых чисел в бинарный формат. В данном случае мы использовали ::bit(8) и получили восьмибитовые строки.
Далее мы можем «сложить» результат в бинарном формате с другой строкой в бинарном формате — 11111111 . Поскольку бинарный AND возвращает единицу только если оба бита это единицы, эта добавочная строка делает вывод интересным.
Практически все остальные поразрядные операторы используют тот же формат:
Поразрядный оператор «not» (
) немного отличается. Он применяется к одному термину, так же, как и обычный оператор NOT :
И, наконец, самый полезный из поразрядных операторов: конкатенация.
Этот оператор обычно используется для склейки вместе строк текста. Например, если мы хотим составить вычисленное «полное имя» для пользователей, мы можем воспользоваться конкатенацией:
Здесь мы для создания значения name сконкатенировали (скомбинировали) first_name , пробел ( ‘ ‘ ) и last_name .
Заключение
Итак, мы рассмотрели практически все операторы фильтрации, котоыре вам могут понадобиться в работе!
Есть еще несколько, о которых мы не упоминали, но они либо используются не слишком часто, либо используются точно так же, как те, что мы разобрали, так что у вас не должно возникнуть проблем с ними.
От редакции Techrocks: возможно, вам будет интересна еще одна статья того же автора: SQL JOIN: руководство по объединению таблиц.
Оператор EXISTS в SQL
Оператор EXISTS выполняет внешний запрос SQL, если внутренний запрос (подзапрос) не возвращает NULL . Принцип работа оператора EXISTS :
Этот процесс повторяется для каждой строки внешнего запроса.
Рассмотрим пример. Предположим, нам нужно вывести всех клиентов, совершивших заказ. В подзапросе мы проверяем наличие совершенного заказа в клиента (по полю customer_id) и если это подтверждается, то в результате выводим идентификатор и имя клиента.
Как проверить наличие записи в таблице sql
Оператор EXISTS проверяет, возвращает ли подзапрос какое-либо значение. Как правило, этот оператор используется для индикации того, что как минимум одна строка в таблице удовлетворяет некоторому условию. Поскольку возвращения набора строк не происходит, то подзапросы с подобным оператором выполняются довольно быстро.
Применение оператора имеет следующий формальный синтаксис:
Например, найдем все товары из таблицы Products, на которые есть заказы в таблице Orders:
Если мы хотим узнать, наоброт, есть ли в таблице строки, которые НЕ удовлетворяют условию, то можно использовать операторы NOT EXISTS . Например, найдем все товары из таблицы Products, на которые не было заказов в таблице Orders:
Стоит отметить, что для получения подобного результата можно было бы использовать и опеатор IN :
Но поскольку при применении EXISTS не происходит выборка строк, то его использование более оптимально и эффективно, чем использование оператора IN.