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);
Advertisements