How to check if a record exists in another table in MySQL

In this tutorial, we are going to see how to use MySQL EXISTS operator to check if a data exists in a table and when to use it to improve query performance.
 

EXISTS operator

EXISTS operator is a boolean operator that returns true or false. EXISTS operator is often used to check the existence of rows returned by a subquery.
 

 
The basic syntax of EXISTS operator:

SELECT 
    column1, column2, ..., column_n
FROM
    table_name
WHERE
    [NOT] EXISTS(subquery);

If the subquery returns at least one row, the EXISTS operator returns true, otherwise, it returns false.

In addition, the EXISTS operator immediately terminates further processing as soon as it finds a matching row, which can help improve query performance.

NOT EXISTS operator returns true if the subquery returns no rows, otherwise it returns false.
 

Example:

In the example below, we have two tables, called “Clients” and “Orders”, which contain the following data:
 

 

 

 
The following statement returns TRUE if there is a customer whose total order price is less than 200:

SELECT Name
FROM Clients
WHERE EXISTS (
       SELECT OrderNbr
           FROM Orders
           WHERE Orders.ClientID = Clients.ClientID 
           AND Total < 200
);

Output:

+----------+
| Name     |
+----------+
| Alex     |
+----------+

As you can see the client “Alex” has the total order price less than 200. So the subquery returns one row, the EXISTS operator returns true.
 

 
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More