SQL ARE

What is the SQL ARE keyword?

Auxiliary reserved word that forms plural predicates such as ARE [NOT] NULL or ARE DISTINCT FROM for row-value expressions.

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 ARE:

SQL ARE Full Explanation

ARE is a reserved keyword in the SQL standard but never appears as a stand-alone statement. Instead, it functions as an auxiliary verb in predicates that apply to row-value or multi-column constructs. When you need to test a set of columns together—for example, to ensure none of them is NULL or to compare two composite keys—you can use plural predicates whose grammar includes ARE. Although the same logic can often be expressed with AND conditions on individual columns, ARE keeps the query concise and guarantees atomic evaluation of the row value as a single unit.Key contexts1. Row NULL test – (, , …) ARE [NOT] NULL checks whether every element in the row is (or is not) NULL.2. Row distinctness – (, ) ARE [NOT] DISTINCT FROM (, ) provides NULL-safe equality comparison between two row values.3. Some vendor features – IBM Db2, BigQuery, and Firebird surface ARE inside extension clauses such as PERIOD definitions or enforced constraints.Caveats- ARE predicates require parentheses around each row-value expression.- Not every dialect implements ARE even if it parses as a reserved word; MySQL and SQL Server reject the syntax.- When mixing scalar and row comparisons, remember that scalar predicates use IS while row predicates use ARE in strict standard form.

SQL ARE Syntax

-- Row NULL predicate
(<value1>, <value2>, ...) ARE [NOT] NULL;

-- Row distinctness predicate
(<value1>, <value2>) ARE [NOT] DISTINCT FROM (<value3>, <value4>);

SQL ARE Parameters

Example Queries Using SQL ARE

-- Return customers that have both email and phone present
SELECT *
FROM customers
WHERE (email, phone) ARE NOT NULL;

-- Compare composite keys between two tables
SELECT i.*
FROM inventory i
JOIN suppliers s
  ON (i.product_id, i.warehouse_id) ARE DISTINCT FROM (s.product_id, s.warehouse_id);

Expected Output Using SQL ARE

First query filters out any customer rows where either email or phone is NULL
Second query returns inventory rows whose composite key does not exactly match the supplier composite key, treating NULLs as equal values rather than unknowns

Use Cases with SQL ARE

Validate that a group of columns are all populated before processing.
Perform NULL-safe equality tests on composite keys without writing lengthy IS NULL OR logic.
Enforce data-quality rules in CHECK constraints that involve multiple columns.
Simplify comparisons in slowly changing dimension or CDC pipelines where NULL handling is critical.

Common Mistakes with SQL ARE

Using ARE with a single column (use IS instead).
Omitting parentheses around row expressions.
Expecting the syntax to work in MySQL or SQL Server, which do not support ARE predicates.
Forgetting that ARE NULL checks evaluate the entire row as one unit, not column by column with short-circuit logic.

First Introduced In

SQL:1992 (row-value comparison predicates)

Frequently Asked Questions

What is the difference between IS and ARE in SQL?

IS applies to scalar predicates (single values). ARE is used with row-value predicates that involve multiple columns at once.

Can I nest ARE predicates inside subqueries?

Yes. You can place an ARE predicate anywhere a boolean condition is allowed, including WHERE, HAVING, and CHECK clauses.

How do NULLs behave with ARE DISTINCT FROM?

ARE DISTINCT FROM treats two NULLs as equal, unlike the default comparison operator, which returns UNKNOWN when any operand is NULL.

Does ARE impact query performance?

In most engines the planner rewrites ARE predicates into equivalent lower-level operations, so performance impact is negligible. Optimize by indexing the underlying columns as usual.