EXISTS

USAGE

EXISTS (SELECT ...)

Value Name

Description

SELECT ...

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.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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    |
+-------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES