EXISTS Condition

This MariaDB tutorial explains how to use the MariaDB EXISTS condition with syntax and examples.

Description

The MariaDB EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS condition in MariaDB is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery

A SELECT statement that usually starts with SELECT * rather than a list of expressions or column names. MariaDB ignores the list of expressions in the subquery anyways.

Note

• SQL statements that use the EXISTS condition in MariaDB are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

Example - With SELECT Statement

Let's look at an example of how to use the EXISTS condition in the SELECT statement in MariaDB.

For example:

SELECT *

FROM sites

WHERE EXISTS (SELECT *

              FROM pages

              WHERE pages.site_id = sites.site_id);

This MariaDB EXISTS condition example will return all records from the sites table where there is at least one record in the pages table with the matching site_id.

Example - With SELECT Statement using NOT EXISTS

Let's look at an example of how to use the EXISTS condition with the NOT operator in MariaDB.

For example,

SELECT *

FROM sites

WHERE NOT EXISTS (SELECT *

                  FROM pages

                  WHERE pages.site_id = sites.site_id);

This MariaDB EXISTS example will return all records from the sites table where there are no records in the pages table for the given site_id.

Example - With INSERT Statement

Let's look at an example of how to use the EXISTS condition in the INSERT statement in MariaDB.

For example:

INSERT INTO contacts

(contact_id, contact_name)

SELECT site_id, site_name

FROM sites

WHERE EXISTS (SELECT *

              FROM pages

              WHERE pages.site_id = sites.site_id);

Example - With UPDATE Statement

Let's look at an example of how to use the EXISTS condition in the UPDATE statement in MariaDB.

For example:

UPDATE sites

SET site_name = 'Fastread.aitechtonic.com'

WHERE EXISTS (SELECT *

              FROM pages

              WHERE pages.site_id = sites.site_id);

Example - With DELETE Statement

Let's look at an example of how to use the EXISTS condition in the DELETE statement in MariaDB.

For example:

DELETE FROM sites

WHERE EXISTS (SELECT *

              FROM pages

              WHERE pages.site_id = sites.site_id);