SQL IS

What does the SQL IS keyword do?

Evaluates whether an expression is NULL, TRUE, FALSE, or UNKNOWN.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IS: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, DuckDB, Snowflake, BigQuery, Redshift

SQL IS Full Explanation

IS is a comparison keyword used in SQL predicates to test the three-valued logic states of an expression. It returns TRUE when the expression exactly matches the specified logical constant (NULL, TRUE, FALSE, or UNKNOWN) and FALSE otherwise. Because NULL represents an unknown value, the normal equality operator (=) cannot be used to test for it. IS provides a deterministic, standard-compliant way to handle NULL checks and boolean truthiness. Many dialects also support IS NOT to invert the result and IS DISTINCT FROM for null-safe equality, but those are extensions beyond the core IS predicate.Behavior notes:- IS NULL and IS NOT NULL work on any data type.- IS TRUE, IS FALSE, and IS UNKNOWN operate on boolean expressions. Dialects without a native BOOLEAN type (e.g., MySQL before 8.0) treat TRUE as 1 and FALSE as 0.- The predicate short-circuits; no additional evaluation cost beyond the expression itself.- Unlike = NULL, which always yields UNKNOWN, expr IS NULL produces TRUE or FALSE, making it indexable and optimizer-friendly.

SQL IS Syntax

expression IS { NULL | TRUE | FALSE | UNKNOWN }
expression IS NOT { NULL | TRUE | FALSE | UNKNOWN }

SQL IS Parameters

Example Queries Using SQL IS

-- 1. Check for missing email addresses
SELECT id, email
FROM users
WHERE email IS NULL;

-- 2. Filter rows where a flag column is explicitly false
SELECT *
FROM feature_flags
WHERE enabled IS FALSE;

-- 3. Exclude NULL order totals when calculating average
SELECT AVG(total_amount) AS avg_total
FROM orders
WHERE total_amount IS NOT NULL;

-- 4. Demonstrate three-valued logic
SELECT 1 IS NULL   AS one_is_null,
       NULL IS NULL AS null_is_null,
       (price > 100) IS UNKNOWN AS price_unknown
FROM products LIMIT 1;

Expected Output Using SQL IS

Returns user rows whose email column is NULL.
Returns rows where enabled = FALSE, ignoring NULLs.
AVG calculated only on non-NULL totals.
Shows FALSE, TRUE, or UNKNOWN results for each predicate.

Use Cases with SQL IS

Detect missing or optional data (IS NULL / IS NOT NULL)
Filter boolean columns while respecting NULL semantics (IS TRUE / IS FALSE)
Build partial indexes or constraints that depend on NULL checks
Write portable predicates that avoid non-standard comparisons to NULL

Common Mistakes with SQL IS

Using = NULL or <> NULL instead of IS NULL / IS NOT NULL.
Forgetting that IS TRUE or IS FALSE ignores NULLs, leading to unexpected exclusions.
Assuming IS UNKNOWN is widely used; many developers overlook it even though it is in the SQL standard.

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between = NULL and IS NULL?

Using = NULL or <> NULL always yields UNKNOWN. IS NULL and IS NOT NULL correctly test for nullability and return TRUE or FALSE.

Does IS TRUE work without a BOOLEAN column type?

In systems like MySQL or SQLite, TRUE maps to 1 and FALSE to 0, so IS TRUE checks for nonzero and IS FALSE for zero. However, a NULL will not satisfy either.

How do IS predicates affect query performance?

IS NULL and IS NOT NULL are generally sargable. Many databases can use indexes or filtered indexes when these predicates appear in WHERE clauses.

When should I use IS UNKNOWN?

Use IS UNKNOWN when you must explicitly capture rows where a boolean expression cannot be determined due to NULL operands, such as in advanced data quality checks.