totn MariaDB

MariaDB: 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 = 'TechOnTheNet.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);
Share on: