FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

Existential Predicates

This group of predicates includes those that use subqueries to submit values for all kinds of assertions in search conditions.Existential predicates are so called because they use various methods to test for the existence or non-existence of some condition, returning TRUE if the existence or non-existence is confirmed or FALSE otherwise.

EXISTS

Syntax
[NOT] EXISTS (<select_stmt>)

The EXISTS predicate uses a subquery expression as its argument.It returns TRUE if the subquery result contains at least one row, otherwise it returns FALSE.

NOT EXISTS returns FALSE if the subquery result contains at least one row, otherwise it returns TRUE.

Note

The subquery can specify multiple columns, or SELECT *, because the evaluation is made on the number of rows that match its criteria, not on the data.
Examples

  1. Find those employees who have projects.

    SELECT *
FROM employee
WHERE EXISTS(SELECT *
             FROM  employee_project ep
             WHERE ep.emp_no = employee.emp_no)

  2. Find those employees who have no projects.

    SELECT *
FROM employee
WHERE NOT EXISTS(SELECT *
                 FROM employee_project ep
                 WHERE ep.emp_no = employee.emp_no)