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 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.
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 );
+----------+ | 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.