Explain SQL exists operator in detail

Time:2021-1-18

Exists operator

The exists operator is used to determine whether there are records in the query clause. If one or more records exist, it returns true; otherwise, it returns false.

SQL exists syntax


SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Demo database

In this tutorial, we will use the runoob sample database.

Here is the data selected from the “websites” table:

+----+--------------+---------------------------+-------+---------+
| id | name     | url            | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google    | https://www.google.cm/  | 1   | USA   |
|2. Taobao| https://www.taobao.com/   | 13  | CN   |
|3 | rookie course| http://www.runoob.com/   | 4689 | CN   |
|4 | microblog| http://weibo.com/      | 20  | CN   |
| 5 | Facebook   | https://www.facebook.com/ | 3   | USA   |
+----+--------------+---------------------------+-------+---------+

Here’s “access.”_ Log “data of website access record table:


mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date    |
+-----+---------+-------+------------+
|  1 |    1 |  45 | 2016-05-10 |
|  2 |    3 |  100 | 2016-05-13 |
|  3 |    1 |  230 | 2016-05-14 |
|  4 |    2 |  10 | 2016-05-14 |
|  5 |    5 |  205 | 2016-05-14 |
|  6 |    4 |  13 | 2016-05-15 |
|  7 |    3 |  220 | 2016-05-15 |
|  8 |    5 |  545 | 2016-05-16 |
|  9 |    3 |  201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL exists instance

Now we want to find out if a website with a total number of visits (count field) greater than 200 exists.

We use the following SQL statement:


SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

After executing the above SQL, the output is as follows:

Exists can be used with not to find records that do not match the query statement


SELECT Websites.name, Websites.url 
FROM Websites 
WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

After executing the above SQL, the output is as follows:

The above is the detailed content of SQL exists operator. For more information about SQL exists operator, please pay attention to other related articles of developer!