Sql как проверить наличие записи в таблице

Best way to test if a row exists in a MySQL table

I’m trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this:

and check to see if the total is non-zero or is it better to do a query like this:

and check to see if any rows were returned?

In both queries, the WHERE clause uses an index.

12 Answers 12

You could also try EXISTS :

and per the documentation, you can SELECT anything.

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

I have made some researches on this subject recently. The way to implement it has to be different if the field is a TEXT field, a non unique field.

I have made some tests with a TEXT field. Considering the fact that we have a table with 1M entries. 37 entries are equal to ‘something’:

• SELECT * FROM test WHERE text LIKE ‘%something%’ LIMIT 1 with mysql_num_rows() : 0.039061069488525s. (FASTER)
• SELECT count(*) as count FROM test WHERE text LIKE ‘%something% : 16.028197050095s.
• SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE ‘%something%’) : 0.87045907974243s.
• SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE ‘%something%’ LIMIT 1) : 0.044898986816406s.

But now, with a BIGINT PK field, only one entry is equal to ‘321321’ :

• SELECT * FROM test2 WHERE LIMIT 1 with mysql_num_rows() : 0.0089840888977051s.
• SELECT count(*) as count FROM test2 WHERE : 0.00033879280090332s.
• SELECT EXISTS(SELECT 1 FROM test2 WHERE ) : 0.00023889541625977s.
• SELECT EXISTS(SELECT 1 FROM test2 WHERE LIMIT 1) : 0.00020313262939453s. (FASTER)

A short example of @ChrisThompson’s answer

Example:

In my research, I can find the result getting on following speed.

I feel it is worth pointing out, although it was touched on in the comments, that in this situation:

This is because the first query can be satisfied by the index, whereas the second requires a row look up (unless possibly all the table’s columns are in the index used).

Adding the LIMIT clause allows the engine to stop after finding any row.

The first query should be comparable to:

Which sends the same signals to the engine (1/* makes no difference here), but I’d still write the 1 to reinforce the habit when using EXISTS :

It may make sense to add the EXISTS wrapping if you require an explicit return when no rows match.

Оператор EXISTS в SQL

Оператор EXISTS выполняет внешний запрос SQL, если внутренний запрос (подзапрос) не возвращает NULL . Принцип работа оператора EXISTS :

Этот процесс повторяется для каждой строки внешнего запроса.

Рассмотрим пример. Предположим, нам нужно вывести всех клиентов, совершивших заказ. В подзапросе мы проверяем наличие совершенного заказа в клиента (по полю customer_id) и если это подтверждается, то в результате выводим идентификатор и имя клиента.

SQL Exists Explained in Detail [Practical Examples]

The result of SQL Exists is a Boolean value TRUE or FALSE, if the subquery returns one or more records it returns TRUE otherwise it returns FALSE
SQL Exists can be used in a SELECT, UPDATE, or DELETE statement

SQL Exists Syntax

• column_name1 | Expression1: It specifies the list of columns or expressions to be retrieved
• table_name : It is the name of the table from which we want to retrieve data, if we want to retrieve data from more than one table we can specify using SQL joins, There must be at least one table listed in the FROM clause.
• WHERE EXISTS: SQL Exist operator specified with where clause to check for the resulting recordset of subquery
• WHERE condition: It is an optional argument with SQL Select, the records which are satisfied this condition will be retrieved

Return Type of SQL Exists

SQL Exists returns value in Boolean Datatype either TRUE or FALSE

Examples of SQL Exists

Consider the hospital management database with four tables patient, doctor, laboratory, and bill to perform practical examples of SQL Exists
Patient Table

patient_id	name	age	gender	address	disease	doctor_id
1	reema	23	female	althan,Surat	fever	21
2	kusum	50	female	vadodara	heart failure	22
3	carlin	43	male	vapi	infection	23
4	rahul	26	male	navsari	cancer	21
6	hansha	55	female	vapi	diabetes	22

Doctor Table

doctor_id	name	age	gender	address
21	asif	55	male	baruch
22	dhawal	40	male	Surat
23	krishna	39	female	Surat
24	lissa	35	female	Navsari
25	leeba	34	female	baruch
26	vini	33	female	Surat
27	Dhiren	32	male	Navsari

Bill Table

bill_no	patient_id	doctor_id	room_charge	no_of_days
5005	1	340	500	4
5006	2	600	480	8
5008	3	800	340	3
5009	4	780	890	6
5010	3	400	1
5011	1	200	300	1
5012	2	600	110	2
5013	3	330	210	1
5014	1	230	340	2

Laboratory Table

lan_no	patient_id	doctor_id	date	amount
10	1	21	02-02-2000	4000
20	2	21	09-09-2001	300
30	3	22	03-03-2001	600
40	1	23	02-06-2002	800
50	4	21	05-07-2003	900
60	2	25	10-04-2004	550
70	4	22	03-04-2005	900

SQL Exists compare with Null value

Example 1: Write SQL query to display patient details with the null condition to be compared with SQL Exists

• In the above query, the subquery returned a result set that contains NULL which causes the EXISTS operator to evaluate to TRUE
• When we execute the above query, the result will contains all records of patient table

OUTPUT:

SQL Exists with subquery returns more than one records

Example 2: Write SQL query to display whose patient details whose data exist in laboratory table

• In the above query, the subquery will return each patient’s laboratory records whose patient id matches with the patient table’s patient id
• SQL Exists with where clause in the outer query will check for TRUE and FALSE value as subquery returns more than one record, where exist will be true

OUTPUT:

SQL Exists with SQL JOIN

Example 3: Write SQL query to display patient information with their disease and doctor name if that patient record exists in laboratory table

• In the above query, the subquery will returns each patient’s laboratory records whose patient id is matches with patient table’s patient id
• In the outer query we have use SQL Left outer join is used to get combine record of both the table patient and doctor
• SQL Exists with where clause in the outer query will check for TRUE and FALSE value , subquery will returns record set so the where condition will be true

OUTPUT:

SQL Exists with FALSE condition

Example 4: Write SQL query to retrieve patient information with doctor name if the patient laboratory billing amount is more than 1000

• In the above query, the subquery will return patient laboratory details if the lab billing amount is greater than 1000
• In the outer query, we have used SQL Left outer join is used to get combine records of both the table patient and doctor
• SQL Exists will return FALSE value as subquery will not return any record because there is no laboratory billing amount is more than 1000

OUTPUT:

Example 5: Write SQL query to retrieve patient information with their doctor name if the doctor is having more than one patient

• In the above query, the SQL Exists operator is used in the conjunction with nested subquery which counts the total number of the doctor who is having more than one patient
• In outer query SQL left join is used to retrieve join records from two tables patient and doctor
• The SQL Exists will return TRUE value as the result of subquery will contain records

OUTPUT:

SQL Exists with Delete Statement

SQL Exist can also be used with SQL Delete statement to conditionally delete the record from the table, if the result of SQL Exists is TRUE then the records will be deleted from the table

Example 6: Write SQL query to remove those doctor information whose do not having any patient

• In the above query, the SQL Exists operator is used in the conjunction with nested subquery which counts the total number of the doctor who does not have any patient
• The SQL Delete statement will remove all doctors information from the doctor table whose data exists in the resulting recordset of the subquery
• When we execute the above query four records will be deleted from the doctor table

OUTPUT:

SQL Exists with Update Statement

SQL Exists can be used to conditionally update the records of table based on the TRUE result of where exists condition in the subquery

Example 7: Write SQL query to update patient billing data if the patient admitted for more than a day in hospital than set room charges to 200

• In the above query, SQL Exists is used to update the patient billing information,
• In the subquery, SQL select statement is used to fetch data from bill table if the no_of_days is greater than 1 and room_charges <200
• SQL where exists will be resulting TRUE value as there is one record where this condition will be true
• When we execute the above update query, it will update room_charges data of one record

OUTPUT:

SQL NOT Exists

• SQL NOT EXISTS condition consists of two logical operators: EXISTS and NOT in which NOT is used to negate a Boolean input
• Unlike EXISTS, NOT EXISTS returns TRUE if the result of the subquery does not contain any rows

Example 8: Write SQL query to retrieve doctor details whose do not having any patient

• In the above query, SQL NOT Exists is used to negate the records doctor who has patient record in the patient table
• The subquery will return the list of patient details with doctor id
• SQL NOT Exists will return true for the doctor whose id is not in the patient table doctor id

OUTPUT:

Comparison between EXISTS and IN

• SQL Exists operator evaluates for TRUE or FALSE value, whereas the IN clause compare all records fetched from the given subquery column with the column value of the outer query
• SQL EXISTS operator can only be used with subqueries, whereas we can use the IN operator on subqueries and values both

Example 9: Write an SQL query to display all patient information whose laboratory report data to exist in laboratory table

Using SQL Exists

• In the above query, SQL Exists is used to fetch patient records whose laboratory details are present in the laboratory table
• In subquery will return records of lab reports matches with the patient id of the patient table , so the SQL Exists condition will return TRUE value

Using SQL IN

• In the above query, SQL IN clause is used to fetch patient data whose laboratory test report records is present in laboratory table
• The subquery will fetch patient id from laboratory table and the resulting patient id will be compared with the patient id of patient table using IN operator, if found records will be display

OUTPUT:

Summary

In this Article on SQL Exists, we have covered the overview of the SQL Exists operator, usage and syntax of how to use SQL Exist with an explanation of each syntax argument, also covered the practical examples of SQL Exists with a NULL value, with a TRUE and FALSE value, with DELETE and UPDATE Statement, SQL NOT Exists example and in the ending section we have explained the difference between SQL Exists and SQL IN with practical example

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: руководство по объединению таблиц.