SQL EMPTY

What is the SQL EMPTY operator in Oracle?

EMPTY is an Oracle SQL operator that checks whether a collection (nested table or varray) contains no elements.

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 EMPTY: Oracle (native support). Other major databases do not implement IS EMPTY for collections.

SQL EMPTY Full Explanation

In Oracle, a collection expression followed by the operator IS EMPTY returns TRUE when the collection has zero elements and FALSE otherwise. It is primarily used with PL/SQL-defined nested tables or varrays that are stored as columns or variables. The operator is evaluated at run time and does not raise an error if the collection is NULL; instead, NULL IS EMPTY yields NULL, preserving three-valued logic. EMPTY improves readability compared with COUNT(collection)=0 and avoids the performance cost of counting elements.

SQL EMPTY Syntax

collection_expression IS EMPTY

SQL EMPTY Parameters

Example Queries Using SQL EMPTY

-- Check a nested table column in a SELECT
SELECT customer_id
FROM   customers
WHERE  orders IS EMPTY;

-- Use in PL/SQL to branch logic
IF l_items IS EMPTY THEN
  DBMS_OUTPUT.put_line('No items found');
END IF;

Expected Output Using SQL EMPTY

The predicate returns TRUE for rows or variables where the collection has no elements, allowing those rows to be selected or the branch to execute

Use Cases with SQL EMPTY

Filter rows whose nested table column is empty
Branch program logic in PL/SQL when a collection variable has no elements
Replace verbose COUNT(collection)=0 comparisons for clarity and slight performance gain

Common Mistakes with SQL EMPTY

Assuming NULL IS EMPTY returns TRUE (it returns NULL)
Using EMPTY with scalar columns; it only works on collection types
Forgetting to include IS (writing orders EMPTY instead of orders IS EMPTY)

First Introduced In

Oracle8i (1998) with introduction of collection types

Frequently Asked Questions

What does IS EMPTY return when the collection is NULL?

It returns NULL, not TRUE. You must coalesce or handle NULL explicitly if you need a Boolean TRUE/FALSE result.

Can I use IS EMPTY on associative arrays?

No. IS EMPTY works only with nested tables and varrays, not with PL/SQL associative arrays (index-by tables).

How do I check for a non-empty collection?

Use IS NOT EMPTY:```IF l_items IS NOT EMPTY THEN -- process itemsEND IF;```

Is IS EMPTY part of the SQL standard?

No. It is an Oracle-specific extension and is not available in PostgreSQL, MySQL, SQL Server, or SQLite.