4.2. Predicates
A predicate is a simple expression asserting some fact, let’s call it P
.
If P
resolves as TRUE, it succeeds.
If it resolves to FALSE or NULL (UNKNOWN), it fails.
A trap lies here, though: suppose the predicate, P
, returns FALSE.
In this case NOT(P)
will return TRUE.
On the other hand, if P
returns NULL (unknown), then NOT(P)
returns NULL as well.
In SQL, predicates can appear in CHECK
constraints, WHERE
and HAVING
clauses, CASE
expressions, the IIF()
function and in the ON
condition of JOIN
clauses, and anywhere a normal expression can occur.
4.2.1. Conditions
A condition — or Boolean expression — is a statement about the data that, like a predicate, can resolve to TRUE, FALSE or NULL.
Conditions consist of one or more predicates, possibly negated using NOT
and connected by AND
and OR
operators.
Parentheses may be used for grouping predicates and controlling evaluation order.
A predicate may embed other predicates.
Evaluation sequence is in the outward direction, i.e. the innermost predicates are evaluated first.
Each level
is evaluated in precedence order until the truth value of the ultimate condition is resolved.
4.2.2. Comparison Predicates
A comparison predicate consists of two expressions connected with a comparison operator. There are six traditional comparison operators:
|=, >, <, >=, <=, <>
For the complete list of comparison operators with their variant forms, see Comparison Operators.
If one of the sides (left or right) of a comparison predicate has NULL
in it, the value of the predicate will be UNKNOWN
.
Retrieve information about computers with the CPU frequency not less than 500 MHz and the price lower than $800:
|
SELECT *
|FROM Pc
|WHERE speed >= 500 AND price < 800;
Retrieve information about all dot matrix printers that cost less than $300:
|
SELECT *
|FROM Printer
|WHERE ptrtype = 'matrix' AND price < 300;
The following query will return no data, even if there are printers with no type specified for them, because a predicate that compares
NULL
withNULL
returnsNULL
:|
SELECT *
|FROM Printer
|WHERE ptrtype = NULL AND price < 300;
On the other hand,
ptrtype
can be tested forNULL
and return a result: it is just that it is not a comparison test:|
SELECT *
|FROM Printer
|WHERE ptrtype IS NULL AND price < 300;
When CHAR
and VARCHAR
fields are compared for equality, trailing spaces are ignored in all cases.
4.2.2.1. Other Comparison Predicates
Other comparison predicates are marked by keyword symbols.
4.2.2.1.1. BETWEEN
Syntax
|
<value> [NOT] BETWEEN <value_1> AND <value_2>
The BETWEEN
predicate tests whether a value falls within a specified range of two values.
(NOT BETWEEN
tests whether the value does not fall within that range.)
The operands for BETWEEN
predicate are two arguments of compatible data types.
The BETWEEN
predicate in Firebird is asymmetrical — if the lower bound is not the first argument, the BETWEEN
predicate will return FALSE.
The search is inclusive (the values represented by both arguments are included in the search).
In other words, the BETWEEN
predicate could be rewritten:
|<value> >= <value_1> AND <value> <= <value_2>
When BETWEEN
is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it is available.
Example
|
SELECT *
|FROM EMPLOYEE
|WHERE HIRE_DATE BETWEEN date '1992-01-01' AND CURRENT_DATE
4.2.2.1.2. LIKE
Syntax
|
<match_value> [NOT] LIKE <pattern>
| [ESCAPE <escape character>]
|
|<match_value> ::= character-type expression
|<pattern> ::= search pattern
|<escape character> ::= escape character
The LIKE
predicate compares the character-type expression with the pattern defined in the second expression.
Case- or accent-sensitivity for the comparison is determined by the collation that is in use.
A collation can be specified for either operand, if required.
4.2.2.1.2.1. Wildcards
Two wildcard symbols are available for use in the search pattern:
the percentage symbol (
%
) will match any sequence of zero or more characters in the tested valuethe underscore character (
_
) will match any single character in the tested value
If the tested value matches the pattern, taking into account wildcard symbols, the predicate is TRUE.
4.2.2.1.2.2. Using the ESCAPE
Character Option
If the search string contains either of the wildcard symbols, the ESCAPE
clause can be used to specify an escape character.
The escape character must precede the
or %
symbol in the search string, to indicate that the symbol is to be interpreted as a literal character._
4.2.2.1.2.3. Examples using LIKE
Find the numbers of departments whose names start with the word
Software
:|
SELECT DEPT_NO
|FROM DEPT
|WHERE DEPT_NAME LIKE 'Software%';
In this example, it is possible to use an index on the DEPT_NAME field if it exists.
ⓘAboutLIKE
and the OptimizerThe
LIKE
predicate itself does not use an index. However, if the predicate takes the form ofLIKE 'string%'
(i.e. the only wildcard is%
at the end), it will be converted toSTARTING WITH 'string'
, which can use an index. This optimization only works for literal patterns, not for parameters.So, if you need to search for the beginning of a string, it is recommended to use the Section 4.2.2.1.3, “
STARTING WITH
” predicate instead of theLIKE
predicate.Search for employees whose names consist of 5 letters, start with the letters
Sm
and end withth
. The predicate will be true for such names asSmith
andSmyth
.|
SELECT
|first_name
|FROM
|employee
|WHERE first_name LIKE 'Sm_th'
Search for all clients whose address contains the string
Rostov
:|
SELECT *
|FROM CUSTOMER
|WHERE ADDRESS LIKE '%Rostov%'
ⓘNoteIf you need to do a case-insensitive search for something enclosed inside a string (
LIKE '%Abc%'
), use of the Section 4.2.2.1.4, “CONTAINING
” predicate is recommended.Search for tables containing the underscore character in their names. The
character is used as the escape character:#
|
SELECT
|RDB$RELATION_NAME
|FROM RDB$RELATIONS
|WHERE RDB$RELATION_NAME LIKE '%#_%' ESCAPE '#'
See alsoSTARTING WITH
, CONTAINING
, SIMILAR TO
4.2.2.1.3. STARTING WITH
Syntax
|
<value> [NOT] STARTING WITH <value>
The STARTING WITH
predicate searches for a string or a string-like type that starts with the characters in its value argument.
The case- and accent-sensitivity of STARTING WITH
depends on the collation of the first value.
When STARTING WITH
is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it exists.
ExampleSearch for employees whose last names start with Jo
:
|SELECT LAST_NAME, FIRST_NAME
|FROM EMPLOYEE
|WHERE LAST_NAME STARTING WITH 'Jo'
See alsoLIKE
4.2.2.1.4. CONTAINING
Syntax
|
<value> [NOT] CONTAINING <value>
The CONTAINING
predicate searches for a string or a string-like type looking for the sequence of characters that matches its argument.
It can be used for an alphanumeric (string-like) search on numbers and dates.
A CONTAINING
search is not case-sensitive.
However, if an accent-sensitive collation is in use then the search will be accent-sensitive.
Search for projects whose names contain the substring
Map
:|
SELECT *
|FROM PROJECT
|WHERE PROJ_NAME CONTAINING 'Map';
Two rows with the names
AutoMap
andMapBrowser port
are returned.Search for changes in salaries with the date containing number 84 (in this case, it means changes that took place in 1984):
|
SELECT *
|FROM SALARY_HISTORY
|WHERE CHANGE_DATE CONTAINING 84;
See alsoLIKE
4.2.2.1.5. SIMILAR TO
Syntax
|
string-expression [NOT] SIMILAR TO <pattern> [ESCAPE <escape-char>]
|
|<pattern> ::= an SQL regular expression
|<escape-char> ::= a single character
SIMILAR TO
matches a string against an SQL regular expression pattern.
Unlike in some other languages, the pattern must match the entire string to succeed — matching a substring is not enough.
If any operand is NULL
, the result is NULL
.
Otherwise, the result is TRUE
or FALSE
.
If a literal pattern is used, and it doesn’t start with a wildcard or other special regex character, SIMILAR TO
can use an index.
4.2.2.1.5.1. Syntax: SQL Regular Expressions
The following syntax defines the SQL regular expression format. It is a complete and correct top-down definition. It is also highly formal and long, and may be daunting to anyone who hasn’t already some experience with regular expressions (or with highly formal, rather long top-down definitions). Feel free to skip it and read the next section, Building Regular Expressions, which uses a bottom-up approach, aimed at the rest of us.
|<regular expression> ::= <regular term> ['|' <regular term> ...]
||
<regular term> ::= <regular factor> ...
||
<regular factor> ::= <regular primary> [<quantifier>]
||
<quantifier> ::= ? | * | + | '{' <m> [,[<n>]] '}'
||
<m>, <n> ::= unsigned int, with <m> <= <n> if both present
||
<regular primary> ::=
|<character> | <character class> | %
|| (<regular expression>)
||
<character> ::= <escaped character> | <non-escaped character>
||
<escaped character> ::=
|<escape-char> <special character> | <escape-char> <escape-char>
||
<special character> ::= any of the characters []()|^-+*%_?{}
||
<non-escaped character> ::=
|any character that is not a <special character>
|and not equal to <escape-char> (if defined)
||
<character class> ::=
|'_' | '[' <member> ... ']' | '[^' <non-member> ... ']'
|| '[' <member> ... '^' <non-member> ... ']'
||
<member>, <non-member> ::= <character> | <range> | <predefined class>
||
<range> ::= <character>-<character>
||
<predefined class> ::= '[:' <predefined class name> ':]'
||
<predefined class name> ::=
|ALPHA | UPPER | LOWER | DIGIT | ALNUM | SPACE | WHITESPACE
4.2.2.1.5.2. Building Regular Expressions
In this section are the elements and rules for building SQL regular expressions.
Characters
Within regular expressions, most characters represent themselves. The only exceptions are the special characters below:
|[ ] ( ) | ^ - + * % _ ? { }
... and the escape character, if it is defined.
A regular expression that contains no special characters or escape characters matches only strings that are identical to itself (subject to the collation in use).
That is, it functions just like the
operator:=
|'Apple' similar to 'Apple' -- true
|'Apples' similar to 'Apple' -- false
|'Apple' similar to 'Apples' -- false
|'APPLE' similar to 'Apple' -- depends on collation
Wildcards
The known SQL wildcards
and _
match any single character and a string of any length, respectively:%
|'Birne' similar to 'B_rne' -- true
|'Birne' similar to 'B_ne' -- false
|'Birne' similar to 'B%ne' -- true
|'Birne' similar to 'Bir%ne%' -- true
|'Birne' similar to 'Birr%ne' -- false
Notice how
also matches the empty string.%
Character Classes
A bunch of characters enclosed in brackets define a character class. A character in the string matches a class in the pattern if the character is a member of the class:
|'Citroen' similar to 'Cit[arju]oen' -- true
|'Citroen' similar to 'Ci[tr]oen' -- false
|'Citroen' similar to 'Ci[tr][tr]oen' -- true
As can be seen from the second line, the class only matches a single character, not a sequence.
Within a class definition, two characters connected by a hyphen define a range. A range comprises the two endpoints and all the characters that lie between them in the active collation. Ranges can be placed anywhere in the class definition without special delimiters to keep them apart from the other elements.
|'Datte' similar to 'Dat[q-u]e' -- true
|'Datte' similar to 'Dat[abq-uy]e' -- true
|'Datte' similar to 'Dat[bcg-km-pwz]e' -- false
Predefined Character Classes
The following predefined character classes can also be used in a class definition:
[:ALPHA:]
Latin letters a..z and A..Z. With an accent-insensitive collation, this class also matches accented forms of these characters.
[:DIGIT:]
Decimal digits 0..9.
[:ALNUM:]
Union of
[:ALPHA:]
and[:DIGIT:]
.[:UPPER:]
Uppercase Latin letters A..Z. Also matches lowercase with case-insensitive collation and accented forms with accent-insensitive collation.
[:LOWER:]
Lowercase Latin letters a..z. Also matches uppercase with case-insensitive collation and accented forms with accent-insensitive collation.
[:SPACE:]
Matches the space character (ASCII 32).
[:WHITESPACE:]
Matches horizontal tab (ASCII 9), linefeed (ASCII 10), vertical tab (ASCII 11), formfeed (ASCII 12), carriage return (ASCII 13) and space (ASCII 32).
Including a predefined class has the same effect as including all its members. Predefined classes are only allowed within class definitions. If you need to match against a predefined class and nothing more, place an extra pair of brackets around it.
|'Erdbeere' similar to 'Erd[[:ALNUM:]]eere' -- true
|'Erdbeere' similar to 'Erd[[:DIGIT:]]eere' -- false
|'Erdbeere' similar to 'Erd[a[:SPACE:]b]eere' -- true
|'Erdbeere' similar to [[:ALPHA:]] -- false
|'E' similar to [[:ALPHA:]] -- true
If a class definition starts with a caret, everything that follows is excluded from the class. All other characters match:
|'Framboise' similar to 'Fra[^ck-p]boise' -- false
|'Framboise' similar to 'Fr[^a][^a]boise' -- false
|'Framboise' similar to 'Fra[^[:DIGIT:]]boise' -- true
If the caret is not placed at the start of the sequence, the class contains everything before the caret, except for the elements that also occur after the caret:
|'Grapefruit' similar to 'Grap[a-m^f-i]fruit' -- true
|'Grapefruit' similar to 'Grap[abc^xyz]fruit' -- false
|'Grapefruit' similar to 'Grap[abc^de]fruit' -- false
|'Grapefruit' similar to 'Grap[abe^de]fruit' -- false
||
'3' similar to '[[:DIGIT:]^4-8]' -- true
|'6' similar to '[[:DIGIT:]^4-8]' -- false
Lastly, the already mentioned wildcard
is a character class of its own, matching any single character._
Quantifiers
A question mark (
) immediately following a character or class indicates that the preceding item may occur 0 or 1 times to match:?
|'Hallon' similar to 'Hal?on' -- false
|'Hallon' similar to 'Hal?lon' -- true
|'Hallon' similar to 'Halll?on' -- true
|'Hallon' similar to 'Hallll?on' -- false
|'Hallon' similar to 'Halx?lon' -- true
|'Hallon' similar to 'H[a-c]?llon[x-z]?' -- true
An asterisk (
) immediately following a character or class indicates that the preceding item may occur 0 or more times to match:*
|'Icaque' similar to 'Ica*que' -- true
|'Icaque' similar to 'Icar*que' -- true
|'Icaque' similar to 'I[a-c]*que' -- true
|'Icaque' similar to '_*' -- true
|'Icaque' similar to '[[:ALPHA:]]*' -- true
|'Icaque' similar to 'Ica[xyz]*e' -- false
A plus sign (
) immediately following a character or class indicates that the preceding item must occur 1 or more times to match:+
|'Jujube' similar to 'Ju_+' -- true
|'Jujube' similar to 'Ju+jube' -- true
|'Jujube' similar to 'Jujuber+' -- false
|'Jujube' similar to 'J[jux]+be' -- true
|'Jujube' sililar to 'J[[:DIGIT:]]+ujube' -- false
If a character or class is followed by a number enclosed in braces (
and {
), it must be repeated exactly that number of times to match:}
|'Kiwi' similar to 'Ki{2}wi' -- false
|'Kiwi' similar to 'K[ipw]{2}i' -- true
|'Kiwi' similar to 'K[ipw]{2}' -- false
|'Kiwi' similar to 'K[ipw]{3}' -- true
If the number is followed by a comma (
), the item must be repeated at least that number of times to match:,
|'Limone' similar to 'Li{2,}mone' -- false
|'Limone' similar to 'Li{1,}mone' -- true
|'Limone' similar to 'Li[nezom]{2,}' -- true
If the braces contain two numbers separated by a comma, the second number not smaller than the first, then the item must be repeated at least the first number and at most the second number of times to match:
|'Mandarijn' similar to 'M[a-p]{2,5}rijn' -- true
|'Mandarijn' similar to 'M[a-p]{2,3}rijn' -- false
|'Mandarijn' similar to 'M[a-p]{2,3}arijn' -- true
The quantifiers
, ?
and *
are shorthand for +
{0,1}
, {0,}
and {1,}
, respectively.
OR-ing Terms
Regular expression terms can be OR’ed with the
operator.
A match is made when the argument string matches at least one of the terms:|
|'Nektarin' similar to 'Nek|tarin' -- false
|'Nektarin' similar to 'Nektarin|Persika' -- true
|'Nektarin' similar to 'M_+|N_+|P_+' -- true
Subexpressions
One or more parts of the regular expression can be grouped into subexpressions (also called subpatterns) by placing them between parentheses (
and (
).
A subexpression is a regular expression in its own right.
It can contain all the elements allowed in a regular expression, and can also have quantifiers added to it.)
|'Orange' similar to 'O(ra|ri|ro)nge' -- true
|'Orange' similar to 'O(r[a-e])+nge' -- true
|'Orange' similar to 'O(ra){2,4}nge' -- false
|'Orange' similar to 'O(r(an|in)g|rong)?e' -- true
Escaping Special Characters
To match against a character that is special in regular expressions, that character has to be escaped. There is no default escape character; the user specifies one when needed:
|'Peer (Poire)' similar to 'P[^ ]+ \(P[^ ]+\)' escape '\' -- true
|'Pera [Pear]' similar to 'P[^ ]+ #[P[^ ]+#]' escape '#' -- true
|'Päron-äppledryck' similar to 'P%$-ä%' escape '$' -- true
|'Pärondryck' similar to 'P%--ä%' escape '-' -- false
The last line demonstrates that the escape character can also escape itself, if needed.
4.2.2.1.6. IS [NOT] DISTINCT FROM
Syntax
|
<operand1> IS [NOT] DISTINCT FROM <operand2>
Two operands are considered DISTINCT (different) if they have a different value or if one of them is NULL
and the other non-null.
They are considered NOT DISTINCT (equal) if they have the same value or if both of them are NULL
.
IS [NOT] DISTINCT FROM
always returns TRUE or FALSE and never UNKNOWN
(NULL
) (unknown value).
Operators
and =
, conversely, will return <>
UNKNOWN
(NULL
) if one or both operands are NULL
.
Operand values | Result of various predicates | |||
|
|
|
| |
Same value |
|
|
|
|
Different values |
|
|
|
|
Both |
|
|
|
|
One |
|
|
|
|
Examples
|
SELECT ID, NAME, TEACHER
|FROM COURSES
|WHERE START_DAY IS NOT DISTINCT FROM END_DAY;
|
|-- PSQL fragment
|IF (NEW.JOB IS DISTINCT FROM OLD.JOB)
|THEN POST_EVENT 'JOB_CHANGED';
See alsoSection 4.2.2.1.8, “IS [NOT] NULL
”, Section 4.2.2.1.7, “Boolean IS [NOT]
”
4.2.2.1.7. Boolean IS [NOT]
Syntax
|
<value> IS [NOT] { TRUE | FALSE | UNKNOWN }
The IS
predicate with Boolean literal values checks if the expression on the left side matches the Boolean value on the right side.
The expression on the left side must be of type BOOLEAN
, otherwise an exception is raised.
The IS [NOT] UNKNOWN
is equivalent to IS [NOT] NULL
.
The right side of the predicate only accepts the literals TRUE
, FALSE
, UNKNOWN
, and NULL
.
It does not accept expressions.
Using the IS predicate with a Boolean data type
|
-- Checking FALSE value
|SELECT * FROM TBOOL WHERE BVAL IS FALSE;
|
|ID BVAL
|============= =======
|2 <false>
|
|-- Checking UNKNOWN value
|SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN;
|
|ID BVAL
|============= =======
|3 <null>
4.2.2.1.8. IS [NOT] NULL
Syntax
|
<value> IS [NOT] NULL
Since NULL
is not a value, these operators are not comparison operators.
The IS [NOT] NULL
predicate tests that the expression on the left side has a value (IS NOT NULL) or has no value (IS NULL).
ExampleSearch for sales entries that have no shipment date set for them:
|SELECT * FROM SALES
|WHERE SHIP_DATE IS NULL;
IS
predicatesThe IS
predicates take precedence above the others.
4.2.3. 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.
4.2.3.1. 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
.
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.
Find those employees who have projects.
|
SELECT *
|FROM employee
|WHERE EXISTS(SELECT *
|FROM employee_project ep
|WHERE ep.emp_no = employee.emp_no)
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)
4.2.3.2. IN
Syntax
|
<value> [NOT] IN (<select_stmt> | <value_list>)
|
|<value_list> ::= <value_1> [, <value_2> ...]
The IN
predicate tests whether the value of the expression on the left side is present in the set of values specified on the right side.
The set of values cannot have more than 65535 items.
The IN
predicate can be replaced with the following equivalent forms:
|(<value> = <value_1> [OR <value> = <value_2> ...])
||
<value> = { ANY | SOME } (<select_stmt>)
When the IN
predicate is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if a suitable one exists.
Lists that are known to be constant are pre-evaluated as invariants and cached as a binary search tree, making comparisons faster if the condition needs to be tested for many rows or if the value list is long.
In its second form, the IN
predicate tests whether the value of the expression on the left side is present — or not present, if NOT IN
is used — in the result of the subquery on the right side.
The subquery must specify only one column, otherwise the error count of column list and variable list do not match
will occur.
Queries using an IN
predicate with a subquery can be replaced with a similar query using the EXISTS
predicate.
For example, the following query:
|SELECT
|model, speed, hd
|FROM PC
|WHERE
|model IN (SELECT model
|FROM product
|WHERE maker = 'A');
can be replaced with a similar one using the EXISTS
predicate:
|SELECT
|model, speed, hd
|FROM PC
|WHERE
|EXISTS (SELECT *
|FROM product
|WHERE maker = 'A'
|AND product.model = PC.model);
However, a query using NOT IN
with a subquery does not always give the same result as its NOT EXISTS
counterpart.
The reason is that EXISTS
always returns TRUE or FALSE, whereas IN
returns NULL
in one of these two cases:
when the test value is
NULL
and theIN ()
list is not emptywhen the test value has no match in the
IN ()
list and at least one list element isNULL
It is in only these two cases that IN ()
will return NULL
while the EXISTS
predicate will return FALSE
(no matching row found
).
In a search or, for example, an IF (…)
statement, both results mean failure
, and it makes no difference to the outcome.
For the same data, NOT IN ()
will return NULL
, while NOT EXISTS
will return TRUE
, leading to opposite results.
As an example, suppose you have the following query:
|-- Looking for people who were not born
|-- on the same day as any famous New York citizen
|SELECT P1.name AS NAME
|FROM Personnel P1
|WHERE P1.birthday NOT IN (SELECT C1.birthday
|FROM Celebrities C1
|WHERE C1.birthcity = 'New York');
Now, assume that the NY celebrities list is not empty and contains at least one NULL birthday.
Then for every citizen who does not share his birthday with a NY celebrity, NOT IN
will return NULL
, because that is what IN
does.
The search condition is thereby not satisfied and the citizen will be left out of the SELECT
result, which is wrong.
For citizens whose birthday does match with a celebrity’s birthday, NOT IN
will correctly return FALSE
, so they will be left out too, and no rows will be returned.
If the NOT EXISTS
form is used:
|-- Looking for people who were not born
|-- on the same day as any famous New York citizen
|SELECT P1.name AS NAME
|FROM Personnel P1
|WHERE NOT EXISTS (SELECT *
|FROM Celebrities C1
|WHERE C1.birthcity = 'New York'
|AND C1.birthday = P1.birthday);
non-matches will have a NOT EXISTS
result of TRUE
and their records will be in the result set.
If there is any chance of NULL
s being encountered when searching for a non-match, you will want to use NOT EXISTS
.
Find employees with the names
Pete
,Ann
andRoger
:|
SELECT *
|FROM EMPLOYEE
|WHERE FIRST_NAME IN ('Pete', 'Ann', 'Roger');
Find all computers that have models whose manufacturer starts with the letter
A
:|
SELECT
|model, speed, hd
|FROM PC
|WHERE
|model IN (SELECT model
|FROM product
|WHERE maker STARTING WITH 'A');
See alsoEXISTS
4.2.3.3. 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
.
The SELECT *
SINGULAR
predicate can return only two values: TRUE
or FALSE
.
ExampleFind those employees who have only one project.
|SELECT *
|FROM employee
|WHERE SINGULAR(SELECT *
|FROM employee_project ep
|WHERE ep.emp_no = employee.emp_no)
4.2.4. 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>
4.2.4.1. ALL
Syntax
|
<value> <op> ALL (<select_stmt>)
When the ALL
quantifier is used, the predicate is TRUE
if every value returned by the subquery satisfies the condition in the predicate of the main query.
ExampleShow only those clients whose ratings are higher than the rating of every client in Paris.
|SELECT c1.*
|FROM Customers c1
|WHERE c1.rating > ALL
|(SELECT c2.rating
|FROM Customers c2
|WHERE c2.city = 'Paris')
If the subquery returns an empty set, the predicate is TRUE
for every left-side value, regardless of the operator.
This may appear to be contradictory, because every left-side value will thus be considered both smaller and greater than, both equal to and unequal to, every element of the right-side stream.
Nevertheless, it aligns perfectly with formal logic: if the set is empty, the predicate is true for every row in the set.
4.2.4.2. ANY
and SOME
Syntax
|
<value> <op> {ANY | SOME} (<select_stmt>)
The quantifiers ANY
and SOME
are identical in their behaviour.
Both are specified in the SQL standard, and they be used interchangeably to improve the readability of operators.
When the ANY
or the SOME
quantifier is used, the predicate is TRUE
if any of the values returned by the subquery satisfies the condition in the predicate of the main query.
If the subquery returns no rows at all, the predicate is automatically considered as FALSE
.
ExampleShow only those clients whose ratings are higher than those of one or more clients in Rome.
|SELECT *
|FROM Customers
|WHERE rating > ANY
|(SELECT rating
|FROM Customers
|WHERE city = 'Rome')