Skip to main content

IS Operator

The IS operator is used to test whether a value matches specific conditions such as being NULL, TRUE, FALSE, or any other boolean state, making it essential for logical comparisons and handling nullability in SQL.

Syntax

value IS [NOT] condition

value: The column or expression being tested. condition: The condition to check against, typically NULL, TRUE, or FALSE.

  • Returns TRUE if value satisfies the specified condition.

  • Returns FALSE if value does not satisfy the condition.

  • Commonly used with NULL to explicitly test for nullability, as standard equality operators (=) cannot be used to compare NULL values.

Limits

  • IS works with logical and null conditions. It is not suitable for comparing standard values; use equality operators (=) for that purpose.

  • The NOT keyword can be used to reverse the condition, such as IS NOT NULL.

Examples

Testing for NULL

-- Example 1: Check if a value is NULL
> SELECT * 
  FROM employees 
  WHERE manager_id IS NULL;

-- Example 2: Exclude NULL values
> SELECT * 
  FROM employees 
  WHERE manager_id IS NOT NULL;

Testing for BOOLEAN

-- Example 3: Check if a column is TRUE
> SELECT * 
  FROM tasks 
  WHERE is_complete IS TRUE;

-- Example 4: Check if a column is FALSE
> SELECT * 
  FROM tasks 
  WHERE is_complete IS FALSE;

-- Example 5: Check for negated conditions
> SELECT * 
  FROM tasks 
  WHERE is_complete IS NOT TRUE;  -- Equivalent to checking for FALSE or NULL