FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

SINGULAR

Syntax
[NOT] SINGULAR (<select_stmt>)

The SINGULAR predicate takes a subquery as its argument and evaluates it as TRUE if the subquery returns exactly one row, otherwise the predicate is evaluated as FALSE.The subquery may list several output columns since the rows are not returned anyway, they are only tested for (singular) existence.For brevity, people usually specify ‘SELECT *’.The SINGULAR predicate can return only two values: TRUE or FALSE.

Example

Find those employees who have only one project.

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

Quantified Subquery Predicates

A quantifier is a logical operator that sets the number of objects for which this condition is true.It is not a numeric quantity, but a logical one that connects the condition with the full set of possible objects.Such predicates are based on logical universal and existential quantifiers that are recognised in formal logic.

In subquery expressions, quantified predicates make it possible to compare separate values with the results of subqueries;they have the following common form:

<value expression> <comparison operator> <quantifier> <subquery>