SQLでNULL値を含むレコードのJOIN時の挙動と対処法

SELECT NULL = NULL AS equals_null,
       NULL <> NULL AS not_equals_null,
       NOT (NULL = NULL) AS negated_comparison,
       NULL IS NULL AS is_null;

CREATE TEMP TABLE employees (
    employee_id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER
);
CREATE TEMP TABLE departments (
    department_row_id INTEGER NOT NULL PRIMARY KEY,
    department_id INTEGER,
    department_name TEXT,
    active INTEGER NOT NULL
);
INSERT INTO employees (employee_id, name, department_id) VALUES
    (1, 'Alice', 10),
    (2, 'Bob', NULL),
    (3, 'Charlie', 20);
INSERT INTO departments
    (department_row_id, department_id, department_name, active) VALUES
    (101, 10, 'HR', 1),
    (102, 20, 'IT', 0),
    (103, NULL, 'Unknown', 1);

SELECT e.employee_id, e.name,
       e.department_id AS employee_department_id,
       d.department_row_id,
       d.department_id AS matched_department_id,
       d.department_name
FROM employees AS e
INNER JOIN departments AS d
    ON e.department_id = d.department_id
ORDER BY e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name,
       e.department_id AS employee_department_id,
       d.department_row_id,
       d.department_id AS matched_department_id,
       d.department_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id
ORDER BY e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name,
       e.department_id AS employee_department_id,
       d.department_row_id,
       d.department_id AS matched_department_id,
       d.department_name
FROM employees AS e
RIGHT JOIN departments AS d
    ON e.department_id = d.department_id
ORDER BY d.department_row_id, e.employee_id;

SELECT e.employee_id, e.name,
       e.department_id AS employee_department_id,
       d.department_row_id,
       d.department_id AS matched_department_id,
       d.department_name
FROM employees AS e
FULL OUTER JOIN departments AS d
    ON e.department_id = d.department_id
ORDER BY CASE WHEN e.employee_id IS NULL THEN 1 ELSE 0 END,
         e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name, d.department_row_id, d.department_name
FROM employees AS e
JOIN departments AS d
    ON e.department_id IS NOT DISTINCT FROM d.department_id
ORDER BY e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name, d.department_row_id, d.department_name
FROM employees AS e
JOIN departments AS d
    ON (e.department_id IS NOT NULL
        AND d.department_id IS NOT NULL
        AND e.department_id = d.department_id)
       OR (e.department_id IS NULL AND d.department_id IS NULL)
ORDER BY e.employee_id, d.department_row_id;

WITH lhs (id, department_id) AS (
    VALUES (1, NULL), (2, 0)
), rhs (id, department_id) AS (
    VALUES (101, NULL), (102, 0)
)
SELECT l.id AS left_id, r.id AS right_id,
       l.department_id AS left_key, r.department_id AS right_key,
       l.department_id IS NOT DISTINCT FROM r.department_id AS null_safe_equal
FROM lhs AS l
JOIN rhs AS r
    ON COALESCE(l.department_id, 0) = COALESCE(r.department_id, 0)
ORDER BY l.id, r.id;

SELECT employee_id, name,
       COALESCE(CAST(department_id AS TEXT), 'No Department') AS department
FROM employees
ORDER BY employee_id;

WITH contacts (employee_id, address1, address2, address3) AS (
    VALUES (1, NULL, 'Tokyo', 'Osaka'),
           (2, NULL, NULL, NULL)
)
SELECT employee_id, COALESCE(address1, address2, address3) AS address
FROM contacts
ORDER BY employee_id;

SELECT employee_id, name, department_id
FROM employees
WHERE department_id IS NULL
ORDER BY employee_id;

SELECT employee_id, name, department_id
FROM employees
WHERE department_id IS NOT NULL
ORDER BY employee_id;

SELECT employee_id, name,
       CASE WHEN department_id IS NULL THEN 'No Department'
            ELSE CAST(department_id AS TEXT)
       END AS department
FROM employees
ORDER BY employee_id;

SELECT e.employee_id, e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id AND d.active = 1
ORDER BY e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id
WHERE d.active = 1
ORDER BY e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id
WHERE d.department_row_id IS NULL
ORDER BY e.employee_id;

SELECT e.employee_id, e.name, d.department_id, d.department_row_id,
       CASE WHEN d.department_row_id IS NULL THEN 'unmatched'
            ELSE 'matched'
       END AS match_status
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id IS NOT DISTINCT FROM d.department_id
ORDER BY e.employee_id, d.department_row_id;

SELECT e.employee_id, e.name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments AS d
    WHERE e.department_id = d.department_id
)
ORDER BY e.employee_id;

WITH lhs (id, join_key) AS (
    VALUES (1, NULL), (2, NULL)
), rhs (id, join_key) AS (
    VALUES (101, NULL), (102, NULL), (103, NULL)
)
SELECT l.id AS left_id, r.id AS right_id
FROM lhs AS l
JOIN rhs AS r ON l.join_key IS NOT DISTINCT FROM r.join_key
ORDER BY l.id, r.id;

WITH combined AS (
    SELECT e.employee_id, e.name,
           e.department_id AS employee_department_id,
           d.department_row_id,
           d.department_id AS matched_department_id,
           d.department_name
    FROM employees AS e
    LEFT JOIN departments AS d
        ON e.department_id = d.department_id
    UNION ALL
    SELECT e.employee_id, e.name,
           e.department_id,
           d.department_row_id,
           d.department_id,
           d.department_name
    FROM departments AS d
    LEFT JOIN employees AS e
        ON e.department_id = d.department_id
    WHERE e.employee_id IS NULL
)
SELECT employee_id, name, employee_department_id,
       department_row_id, matched_department_id, department_name
FROM combined
ORDER BY CASE WHEN employee_id IS NULL THEN 1 ELSE 0 END,
         employee_id, department_row_id;