- Installation
- Guides
- Data Import & Export
- CSV Import
- CSV Export
- Parquet Import
- Parquet Export
- Query Parquet
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- SQLite Import
- Postgres Import
- Meta Queries
- Python
- Install
- Execute SQL
- Jupyter Notebooks
- SQL on Pandas
- Import From Pandas
- Export To Pandas
- SQL on Arrow
- Import From Arrow
- Export To Arrow
- Relational API on Pandas
- Multiple Python Threads
- DuckDB with Ibis
- DuckDB with Fugue
- DuckDB with Polars
- DuckDB with Vaex
- DuckDB with DataFusion
- DuckDB with fsspec filesystems
- SQL Editors
- Data Viewers
- Documentation
- Connect
- Data Import
- Client APIs
- Overview
- Python
- R
- Java
- Julia
- C
- Overview
- Startup
- Configure
- Query
- Data Chunks
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference
- C++
- Node.js
- Wasm
- ODBC
- CLI
- SQL
- Introduction
- Statements
- Overview
- Select
- Insert
- Delete
- Update
- Create Schema
- Create Table
- Create View
- Create Sequence
- Create Macro
- Drop
- Alter Table
- Copy
- Export
- Attach
- Query Syntax
- SELECT
- FROM
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT
- SAMPLE
- UNNEST
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Data Types
- Overview
- NULL Values
- Boolean
- Enum
- Numeric
- Text
- Date
- Timestamp
- Interval
- Blob
- Bitstring
- List
- Struct
- Map
- Union
- Expressions
- Functions
- Overview
- Enum Functions
- Numeric Functions
- Text Functions
- Pattern Matching
- Date Functions
- Timestamp Functions
- Timestamp With Time Zone Functions
- Time Functions
- Interval Functions
- Date Formats
- Date Parts
- Blob Functions
- Bitstring Functions
- Nested Functions
- Utility Functions
- Indexes
- Aggregates
- Window Functions
- Samples
- Information Schema
- Metadata Functions
- Configuration
- Pragmas
- Extensions
- Development
- DuckDB Repositories
- Testing
- Internals Overview
- Storage Versions & Format
- Execution Format
- Profiling
- Release Dates
- Building
- Benchmark Suite
- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo
NULL
values are special values that are used to represent missing data in SQL. Columns of any type can contain NULL
values. Logically, a NULL
value can be seen as “the value of this field is unknown”.
-- insert a null value into a table
CREATE TABLE integers(i INTEGER);
INSERT INTO integers VALUES (NULL);
NULL
values have special semantics in many parts of the query as well as in many functions:
Any comparison with a
NULL
value returnsNULL
, includingNULL=NULL
.
You can use IS NOT DISTINCT FROM
to perform an equality comparison where NULL
values compare equal to each other. Use IS (NOT) NULL
to check if a value is NULL.
SELECT NULL=NULL;
-- returns NULL
SELECT NULL IS NOT DISTINCT FROM NULL;
-- returns true
SELECT NULL IS NULL;
-- returns true
NULL and Functions
A function that has input argument as NULL
usually returns NULL
.
SELECT COS(NULL);
-- NULL
COALESCE
is an exception to this. COALESCE
takes any number of arguments, and returns for each row the first argument that is not NULL
. If all arguments are NULL
, COALESCE
also returns NULL
.
SELECT COALESCE(NULL, NULL, 1);
-- 1
SELECT COALESCE(10, 20);
-- 10
SELECT COALESCE(NULL, NULL);
-- NULL
NULL and Conjunctions
NULL
values have special semantics in AND
/OR
conjunctions. For the ternary logic truth tables, see the Boolean Type documentation.
NULL and Aggregate Functions
NULL
values are ignored in most aggregate functions.
Aggregate functions that do not ignore NULL
values include: FIRST
, LAST
, LIST
, and ARRAY_AGG
. To exclude NULL
values from those aggregate functions, the FILTER
clause can be used.
CREATE TABLE integers(i INTEGER);
INSERT INTO integers VALUES (1), (10), (NULL);
SELECT MIN(i) FROM integers;
-- 1
SELECT MAX(i) FROM integers;
-- 10