EXISTS and NOT EXISTS in MySQL

  • Avinash
  • 1
  • Dec 08, 2011
  • Database

EXISTS and NOT EXISTS 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 TRUE and NOT EXISTS will be FALSE. And if sub query will return nothing then EXISTS will be FALSE and NOT EXISTS will be TRUE.

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.

SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2);
  SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2);

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.

SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2);
  SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2);

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:

SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.id="1") AND table1.id="1";
  SELECT * FROM table1 WHERE NOT EXISTS
  (SELECT * FROM table2 WHERE table2.id="1") AND table1.id="1";

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

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by:

  • Dinamo

    Thanks It’s was very helpfull