FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 
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).

Example

Search for sales entries that have no shipment date set for them:

SELECT * FROM SALES
WHERE SHIP_DATE IS NULL;
Note
Note regarding the IS predicates

The IS predicates take precedence above the others.

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)

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:

  1. when the test value is NULL and the IN () list is not empty

  2. when the test value has no match in the IN () list and at least one list element is NULL

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.

Tip

If there is any chance of NULLs being encountered when searching for a non-match, you will want to use NOT EXISTS.
Examples of use

  1. Find employees with the names “Pete”, “Ann” and “Roger”:

    SELECT *
FROM EMPLOYEE
WHERE FIRST_NAME IN ('Pete', 'Ann', 'Roger');

  2. 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 also

EXISTS