MySQL EXISTS

In SQL language, the EXISTS statement is used in a conditional clause to find out whether or not rows are present when using a subquery.

Note: this command is not to be confused with the IN clause. The EXISTS statement checks whether the subquery returns a result or not, while IN clause checks whether one or more data matches.
 

 

Syntax:

The basic use of the EXISTS command is to check if a subquery returns a result or not, using EXISTS in the conditional clause. The external query will run only if the internal query returns at least one result.

SELECT column_name1
FROM `table1`
WHERE EXISTS (
    SELECT column_name2
    FROM `table2`
    WHERE column_name3 = 100
  )

In the example above, if there is at least one row in table2 whose column_name3 contains the value 100, then the subquery will return at least one result. Then the condition will be checked and the main query will return the results of column_name1 in table1.
 

Example :

In order to show a practical example, let’s suppose a system composed of a table containing “Orders” and a table containing “Products”.
 
Orders table:

+---------+-----------+----------+-------------+
| OrderID |  NumOrder |   Total  |  ProductID |
+---------+-----------+----------+-------------+
|     1   |   9901    |  254.00  |    101      |
|     2   |   9902    |  300.96  |    111      |
|     3   |   9903    |  145.03  |    120      |
|     4   |   9904    |  744.33  |    102      |
+---------+-----------+----------+-------------+

 

 
Products table:

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | fourniture |  148  | 2       |
|  105 | Gum      | fourniture |  250  | 1       |
+------+----------+------------+-------+---------+

It is possible to perform an SQL query that displays the orders for which there is actually a product. This query can be interpreted as follows:

SELECT *
FROM Orders
WHERE EXISTS (
    SELECT * 
    FROM Products
    WHERE id = ProductID
)

Output:

+---------+-----------+----------+-------------+
| OrderID |  NumOrder |   Total  |  ProductID |
+---------+-----------+----------+-------------+
|     1   |   9901    |  254.00  |    101      |
|     4   |   9904    |  744.33  |    102      |
+---------+-----------+----------+-------------+

 

 
The result shows that only orders n°1 and n°2 have a product in the product table (cf. the condition id = ProductID). This query is interesting because it does not affect the result of the main query, unlike the use of a join which will concatenate the columns of the 2 joined tables.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More