The EXISTS operator in MySQL is a type of Boolean operator which returns the true or false result. It is used in combination with a subquery and checks the existence of data in a subquery. It means if a subquery returns any record, this operator returns true. Otherwise, it will return false. The true value is always represented numeric value 1, and the false value represents 0. We can use it with SELECT, UPDATE, DELETE, INSERT statement.
Syntax
The following are the syntax to use the EXISTS operator in MySQL:
SELECT col_names
FROM tab_name
WHERE [NOT] EXISTS (
SELECT col_names
FROM tab_name
WHERE condition
); The NOT operator is used to negates the EXISTS operator. It returns true when the subquery does not return any row. Otherwise, it returns false.
Generally, the EXISTS query begins with SELECT *, but it can start with the SELECT column, SELECT a_constant, or anything in the subquery. It will give the same output because MySQL ignores the select list in the SUBQUERY.
This operator terminates immediately for further processing after the matching result found. This feature improves the performance of the query in MySQL.
Parameter Explanation
The following are parameters used in the EXISTS operator:
| Parameter Name | Descriptions |
|---|---|
| col_names | It is the name of column(s) that contains in the specified table. |
| tab_name | It is the name of the table from which we are going to perform the EXISTS operator. |
| condition | It specifies for searching the specific value from the table. |
| subquery | It is usually the SELECT statement that begins with SELECT *, but MySQL ignores it in a subquery. |
MySQL EXISTS Operator Example
Let us understand how the EXISTS operator works in MySQL. Here, we are going to first create two tables named “customer” and “orders” using the following statement:
CREATE TABLE customer(
cust_id int NOT NULL,
name varchar(35),
occupation varchar(25),
age int
); CREATE TABLE orders (
order_id int NOT NULL,
cust_id int,
prod_name varchar(45),
order_date date
); Next, we need to insert values into both tables. Execute the below statements:
INSERT INTO customer(cust_id, name, occupation, age)
VALUES (101, 'Peter', 'Engineer', 32),
(102, 'Joseph', 'Developer', 30),
(103, 'John', 'Leader', 28),
(104, 'Stephen', 'Scientist', 45),
(105, 'Suzi', 'Carpenter', 26),
(106, 'Bob', 'Actor', 25),
(107, NULL, NULL, NULL);INSERT INTO orders (order_id, cust_id, prod_name, order_date)
VALUES (1, '101', 'Laptop', '2020-01-10'),
(2, '103', 'Desktop', '2020-02-12'),
(3, '106', 'Iphone', '2020-02-15'),
(4, '104', 'Mobile', '2020-03-05'),
(5, '102', 'TV', '2020-03-20'); To verify the tables, run the SELECT command as below:
msql> SELECT * FROM customer;
AND,
mysql> SELECT * FROM orders;We will get the below output:
MySQL SELECT EXISTS Example
In this example, we are going to use EXISTS operator to find the name and occupation of the customer who has placed at least one order:
mysql> SELECT name, occupation FROM customer
WHERE EXISTS (SELECT * FROM Orders
WHERE customer.cust_id = Orders.cust_id); The following output appears:
Again, if we want to get the name of the customer who has not placed an order, then use the NOT EXISTS operator:
mysql> SELECT name, occupation FROM customer
WHERE NOT EXISTS (SELECT * FROM Orders
WHERE customer.cust_id = Orders.cust_id); It will give the below output:
MySQL EXISTS With DELETE Statement Example
Suppose we want to delete a record from the Orders table whose order_id = 3, execute the following query that deletes the record from Orders table permanently:
mysql> DELETE FROM Orders WHERE EXISTS (
SELECT * FROM customer
WHERE order_id=3); To verify the output, run the below command:
mysql> SELECT * FROM Orders; In the output, we can see that the table record whose order_id=3 is deleted successfully.
If we want to check whether a row exists in a table or not, use the following query:
mysql> SELECT EXISTS(SELECT * from customer WHERE cust_id=104) AS Result; We will get the output 1 that means true. Hence, cust_id=104 exists in the table.
Difference between EXISTS and IN operator
The main differences between the EXISTS and IN operator is given in a tabular form:
| SN | IN | EXISTS |
|---|---|---|
| 1. | It is used to minimize the multiple OR conditions in MySQL. | It is used to check the existence of data in a subquery. |
| 2. | SELECT col_names FROM tab_name WHERE col_name IN (subquery); | SELECT col_names FROM tab_name WHERE [NOT] EXISTS (subquery); |
| 3. | It compares all values inside the IN operator. | It stops for further execution as soon as it finds the first true occurrence. |
| 4. | It can use for comparing NULL values. | It cannot use for comparing NULL values. |
| 5. | It executes faster when the subquery result is less. | It executes faster when the subquery result is large. |
| 6. | It performs a comparison between parent query and child query or subquery. | It does not perform a comparison between parent query and child query or subquery. |
Leave a Reply