NULL値を気にせず日付でソートする方法!MySQLのORDER BYとNULLSオプション
SQLで日付列をソートする際、NULL値の日付を結果セットの後ろに配置したい場合があります。これは、NULL値を明確に区別したい場合や、データ分析の際に都合が良い場合などに役立ちます。
方法
MySQLでは、ORDER BY
句にNULLS FIRST
またはNULLS LAST
キーワードを指定することで、NULL値のソート順序を明示的に指定できます。
例
以下の例では、date
列に基づいて昇順ソートを行い、NULL値の日付を結果セットの後ろに配置します。
SELECT *
FROM your_table
ORDER BY date ASC NULLS LAST;
結果
| id | name | date |
|-----|------|------------|
| 1 | John | 2023-01-01 |
| 2 | Mary | 2023-02-01 |
| 3 | Bob | NULL |
降順ソートの場合は、DESC
キーワードと組み合わせてNULLS FIRST
またはNULLS LAST
キーワードを使用します。
SELECT *
FROM your_table
ORDER BY date DESC NULLS FIRST;
| id | name | date |
|-----|------|------------|
| 3 | Bob | NULL |
| 1 | John | 2023-01-01 |
| 2 | Mary | 2023-02-01 |
補足
NULLS FIRST
とNULLS LAST
は、ORDER BY
句で指定された列に対してのみ適用されます。- 複数の列でソートする場合は、それぞれの列に
NULLS FIRST
またはNULLS LAST
キーワードを指定する必要があります。
CASE
式を使用して、NULL値を特定の値に置き換えてからソートすることもできます。- 特定のデータベースによっては、NULL値のソート順序を指定する別の方法がある場合があります。
注意
- NULL値のソート順序は、データベースの設定によって異なる場合があります。
- NULL値のソート順序を変更する前に、その影響をよく考慮する必要があります。
SELECT *
FROM employees
ORDER BY date_of_birth ASC NULLS LAST;
| id | name | date_of_birth |
|-----|------|------------|
| 1 | John | 1980-01-01 |
| 2 | Mary | 1985-02-01 |
| 3 | Bob | NULL |
降順ソートの場合
SELECT *
FROM employees
ORDER BY date_of_birth DESC NULLS FIRST;
| id | name | date_of_birth |
|-----|------|------------|
| 3 | Bob | NULL |
| 1 | John | 1980-01-01 |
| 2 | Mary | 1985-02-01 |
CASE式を使用する場合
SELECT *
FROM employees
ORDER BY CASE WHEN date_of_birth IS NULL THEN '9999-12-31' ELSE date_of_birth END ASC;
| id | name | date_of_birth |
|-----|------|------------|
| 1 | John | 1980-01-01 |
| 2 | Mary | 1985-02-01 |
| 3 | Bob | NULL |
上記のサンプルコードは、MySQLデータベースを使用しています。他のデータベースを使用する場合は、構文が異なる場合があります。
ORDER BY句にNULLS FIRSTまたはNULLS LASTキーワードを使用する
SELECT *
FROM your_table
ORDER BY date ASC NULLS LAST;
| id | name | date |
|-----|------|------------|
| 1 | John | 2023-01-01 |
| 2 | Mary | 2023-02-01 |
| 3 | Bob | NULL |
SELECT *
FROM your_table
ORDER BY date DESC NULLS FIRST;
| id | name | date |
|-----|------|------------|
| 3 | Bob | NULL |
| 1 | John | 2023-01-01 |
| 2 | Mary | 2023-02-01 |
以下の例では、date
列がNULLの場合、9999-12-31
という値に置き換えてから昇順ソートを行います。
SELECT *
FROM your_table
ORDER BY CASE WHEN date IS NULL THEN '9999-12-31' ELSE date END ASC;
| id | name | date |
|-----|------|------------|
| 1 | John | 2023-01-01 |
| 2 | Mary | 2023-02-01 |
| 3 | Bob | NULL |
サブクエリを使用して、NULL値の日付を別のテーブルに格納し、そのテーブルを結合してソートすることもできます。
以下の例では、date
列がNULLの行をtemp_table
というテーブルに格納し、temp_table
とyour_table
を結合して昇順ソートを行います。
CREATE TABLE temp_table (
id INT,
name VARCHAR(255),
date DATE
);
INSERT INTO temp_table
SELECT id, name, date
FROM your_table
WHERE date IS NULL;
SELECT *
FROM your_table AS t1
LEFT JOIN temp_table AS t2 ON t1.id = t2.id
ORDER BY t1.date ASC NULLS LAST;
| id | name | date |
|-----|------|------------|
| 1 | John | 2023-01-01 |
| 2 | Mary | 2023-02-01 |
| 3 | Bob | NULL |
- NULL値を特定の値に置き換えてソートしたい場合は、
CASE
式を使用する方法が有効です。 - サブクエリを使用する方法は、複雑な処理が必要な場合に有効です。
sql mysql