EXISTS
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Operators for MariaDB Xpand
Topics on this page:
Overview
Presence of returned rows.
USAGE
EXISTS (SELECT ...)
Value Name | Description |
|---|---|
| The sub-select to run for each potential row |
DETAILS
The EXISTS operator runs a SELECT and returns 1 (true) if it returns at least one row or 0 (false) if no rows are returned.
No data values are accessible from this operator's SELECT call, as it only checks for the presence or lack of returned rows. Thus, the requested row values can be anything syntactically valid. For instance, using * works fine.
This operator is typically used in a WHERE clause of an outer SELECT to return only the rows that match certain cross-table conditional expressions. When run in this manner, the sub-select can make use of the outer row values in the inner WHERE clause.
EXAMPLES
CREATE TABLE exists_vendors (
vendor_name VARCHAR(20),
vendor_id BIGINT
);
INSERT INTO exists_vendors VALUES
('Acme', 42),
('Another', 44),
('Beautiful', 55),
('Calming', 77),
('Dazzling', 101);
SELECT EXISTS (
SELECT *
FROM exists_vendors
WHERE vendor_name LIKE 'A%'
) AS result;
+--------+
| result |
+--------+
| 1 |
+--------+
SELECT EXISTS (
SELECT *
FROM exists_vendors
WHERE vendor_name LIKE 'Z%'
) AS result;
+--------+
| result |
+--------+
| 0 |
+--------+
CREATE TABLE exists_products (
product VARCHAR(20),
vendor_id BIGINT
);
INSERT INTO exists_products VALUES
('Box', 42),
('Sign', 42),
('Flower', 55),
('Firework', 101);
SELECT vendor_name
FROM exists_vendors AS v
WHERE EXISTS (
SELECT *
FROM exists_products AS p
WHERE p.vendor_id = v.vendor_id
);
+-------------+
| vendor_name |
+-------------+
| Acme |
| Beautiful |
| Dazzling |
+-------------+
SELECT vendor_name
FROM exists_vendors AS v
WHERE EXISTS (
SELECT *
FROM exists_products AS p
WHERE p.vendor_id = v.vendor_id
AND product LIKE 'F%'
);
+-------------+
| vendor_name |
+-------------+
| Beautiful |
| Dazzling |
+-------------+