EXISTS and NOT EXISTS in MySQL

[code]EXISTS[/code] and [code]NOT EXISTS[/code] are most likely to used when your playing with the subqueries in MySQL.

So these will be used when you want to perform operation based on checking row existance of the other table or even same table.

If sub query will return any value then EXISTS will be [code]TRUE[/code] and NOT EXISTS will be [code]FALSE[/code]. And if sub query will return nothing then EXISTS will be [code]FALSE[/code] and NOT EXISTS will be [code]TRUE[/code].

As per the MySQL documentation, Traditional EXISTS sub query starts with the SELECT *; but here you can start anything like SELECT column, select 5.

Let’s have a quick example of this, have a look at below queries.

[cc lang=”mysql”]
SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2);
[/cc]

Above query will return all rows from table1 if subquery will return atleast one row. If the result set of the subquery is NULL then this whole query will return blank result set.

[cc lang=”mysql”]
SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2);
[/cc]

This example is reverse of the first query. This will return all rows from table1 if table2 is empty, (i.e. No rows in table2)

[box type=”info”]Note: Traditional EXISTS subquery starts with the SELECT *; but here you can start anythin like SELECT column, select 5.[/box]

You can specify any type of WHERE condition in main query and sub query both. Have a look at below example for the same:

[cc lang=”mysql”]
SELECT * FROM table1 WHERE NOT EXISTS
(SELECT * FROM table2 WHERE table2.id=”1″) AND table1.id=”1″;
[/cc]

So above query will return only one row from the table 1 if table2 has specific row which matches the condition in sub query.