{"id":4917,"date":"2013-01-21T23:38:40","date_gmt":"2013-01-22T06:38:40","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4917"},"modified":"2024-01-03T03:26:14","modified_gmt":"2024-01-03T10:26:14","slug":"mysql-exists","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-exists\/","title":{"rendered":"MySQL EXISTS"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL EXISTS<\/code> operator and when to use it to improve the performance of the queries.<\/p>\n\n\n\n

Introduction to MySQL EXISTS operator<\/h2>\n\n\n\n

The EXISTS<\/code> operator is a boolean<\/a> operator that returns either true or false. The EXISTS<\/code> operator is often used to test for the existence of rows returned by the subquery<\/a>.<\/p>\n\n\n\n

The following illustrates the basic syntax of the EXISTS<\/code> operator:<\/p>\n\n\n

SELECT<\/span> \n    select_list\nFROM<\/span>\n    a_table\nWHERE<\/span>\n    [NOT<\/span>] EXISTS<\/span>(subquery);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the subquery returns at least one row, the EXISTS<\/code> operator returns true, otherwise, it returns false.<\/p>\n\n\n\n
In addition, the EXISTS<\/code> operator terminates further processing immediately once it finds a matching row, which can help improve the performance of the query.<\/p>\n\n\n\n
The NOT<\/code> operator negates the EXISTS<\/code> operator. In other words, the NOT EXISTS<\/code> returns true if the subquery returns no row, otherwise it returns false.<\/p>\n\n\n\n
MySQL EXISTS operator examples<\/h2>\n\n\n\n
Let’s take some examples of using the EXISTS<\/code> operator to understand how it works.<\/p>\n\n\n\n
MySQL SELECT EXISTS examples<\/h3>\n\n\n\n
Consider the following customers<\/code> and orders<\/code> tables in the sample database<\/a>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the EXISTS<\/code> operator to find the customer who has at least one order:<\/p>\n\n\n
SELECT<\/span> \n    customerNumber, \n    customerName\nFROM<\/span>\n    customers\nWHERE<\/span>\n    EXISTS<\/span>(\n\tSELECT<\/span> \n            1<\/span>\n        FROM<\/span>\n            orders\n        WHERE<\/span>\n            orders.customernumber \n\t\t= customers.customernumber);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, for each row in the customers<\/code> table, the query checks the customerNumber<\/code> in the orders<\/code> table. If the customerNumber<\/code>, which appears in the customers<\/code> table, exists in the orders<\/code> table, the subquery returns the first matching row. <\/p>\n\n\n\n
As a result, the EXISTS<\/code> operator returns true and stops examining the orders<\/code> table. Otherwise, the subquery returns no row, and the EXISTS<\/code> operator returns false.<\/p>\n\n\n\n
The following example uses the NOT EXISTS<\/code> operator to find customers who do not have any orders:<\/p>\n\n\n
SELECT<\/span> \n    customerNumber, \n    customerName\nFROM<\/span>\n    customers\nWHERE<\/span>\n    NOT<\/span> EXISTS<\/span>( \n\tSELECT<\/span> \n            1<\/span>\n        FROM<\/span>\n            orders\n        WHERE<\/span>\n            orders.customernumber = customers.customernumber\n\t);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL UPDATE EXISTS examples<\/h3>\n\n\n\n
Suppose that you have to update the phone extensions of the employees who work at the office in San Francisco.<\/p>\n\n\n\n
The following statement finds employees who work at the office in San Franciso<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    employeenumber, \n    firstname, \n    lastname, \n    extension\nFROM<\/span>\n    employees\nWHERE<\/span>\n    EXISTS<\/span>( \n        SELECT<\/span> \n            1<\/span>\n        FROM<\/span>\n            offices\n        WHERE<\/span>\n            city = 'San Francisco'<\/span> AND<\/span> \n           offices.officeCode = employees.officeCode);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
This example adds the number 1 to the phone extension of employees who work at the office in San Francisco:<\/p>\n\n\n
UPDATE<\/span> employees \nSET<\/span> \n    extension = CONCAT<\/span>(extension, '1'<\/span>)\nWHERE<\/span>\n    EXISTS<\/span>( \n        SELECT<\/span> \n            1<\/span>\n        FROM<\/span>\n            offices\n        WHERE<\/span>\n            city = 'San Francisco'<\/span>\n                AND<\/span> offices.officeCode = employees.officeCode);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Note that you can use SELECT *<\/code>, SELECT column<\/code>, SELECT a_constant<\/code>, or anything in the subquery. The results are the same because MySQL ignores the select list that appeared in the SELECT<\/a><\/code> clause.<\/p>\n\n\n\n