>Digital-flame

`ORDER BY` places NULL values first or last depending on the database and sort direction.

| Database | ASC default | DESC default |
|---|---|---|
| PostgreSQL, Oracle | NULLs last | NULLs first |
| MySQL, SQL Server | NULLs first | NULLs last |

In PostgreSQL and Oracle, control placement explicitly:

```sql
ORDER BY salary ASC NULLS LAST
ORDER BY salary DESC NULLS FIRST
```

A portable alternative, including MySQL and SQL Server:

```sql
ORDER BY
    CASE WHEN salary IS NULL THEN 1 ELSE 0 END,
    salary ASC;
-- Non-NULL salaries ascending, then NULLs
```

To put NULLs first, use `THEN 0 ELSE 1`.

**Interview answer:** “Default NULL ordering differs between databases. Use NULLS FIRST or NULLS LAST where supported, or a CASE expression to control placement explicitly.”

Arithmetic involving `NULL` produces **NULL**:

```sql
10 + NULL  -- NULL
10 * NULL  -- NULL
```

**COALESCE** returns the first non-NULL argument. Use it to provide a default when that default makes business sense:

```sql
salary + COALESCE(bonus, 0)
-- Treats a missing bonus as zero
```

**NULLIF(a, b)** returns `NULL` if the arguments are equal; otherwise, it returns `a`. Use it to avoid division by zero:

```sql
amount / NULLIF(quantity, 0)
-- If quantity is zero, division returns NULL instead of an error
```

You can combine them:

```sql
COALESCE(amount / NULLIF(quantity, 0), 0)
-- Replaces a NULL result with zero
```

**Pitfall:** A missing or undefined result is not necessarily zero. Only substitute zero when it is meaningful.

**Interview answer:** “NULL propagates through arithmetic expressions. COALESCE supplies defaults for missing values, while NULLIF can turn a zero denominator into NULL to prevent division-by-zero errors.”

In an equality join, `NULL` does not match any value—even another `NULL`—because the comparison evaluates to **UNKNOWN**. Join conditions match only when **TRUE**.

A `LEFT JOIN` preserves unmatched left-hand rows and fills the right-hand columns with `NULL`.

```sql
SELECT *
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'PAID';
```

For unmatched users, `o.status` is `NULL`, so the filter evaluates to `UNKNOWN` and removes them. This query effectively behaves like an `INNER JOIN`.

To preserve all users while joining only paid orders, put the filter in `ON`:

```sql
SELECT *
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id AND o.status = 'PAID';
```

**Interview answer:** “ON determines which rows match; WHERE filters the joined result. A WHERE condition that rejects NULL values from the right-hand table removes unmatched rows from a LEFT JOIN.”

`GROUP BY` and `DISTINCT` treat NULL values as **not distinct from each other**, even though `NULL = NULL` evaluates to `UNKNOWN`.

- **GROUP BY:** All NULL values in a grouping column form one group.
- **DISTINCT:** Multiple NULL values in a selected column become one NULL.

For values `10, 10, NULL, NULL`:

```sql
SELECT value, COUNT(*)
FROM example
GROUP BY value;
-- 10   → 2
-- NULL → 2

SELECT DISTINCT value FROM example;
-- 10
-- NULL
```

With multiple columns, grouping and deduplication consider the **entire combination of values**.

**Interview answer:** “GROUP BY and DISTINCT use ‘not distinct’ semantics rather than ordinary equality. This allows NULL values to belong to the same group or be treated as duplicates.”

Most common aggregates (`SUM`, `AVG`, `MIN`, `MAX`, `COUNT(column)`) **ignore NULL values**.

| Expression | What it counts |
|---|---|
| `COUNT(*)` | All rows, including rows containing NULL |
| `COUNT(column)` | Non-NULL values in that column |
| `COUNT(DISTINCT column)` | Unique non-NULL values in that column |

For values `10, 10, 20, NULL`:

```sql
COUNT(*)                -- 4
COUNT(column)           -- 3
COUNT(DISTINCT column)  -- 2
SUM(column)             -- 40
AVG(column)             -- 40 / 3, not 40 / 4
```

**Edge case:** With no non-NULL input values, `SUM`, `AVG`, `MIN`, and `MAX` return `NULL`; `COUNT(column)` returns `0`.

**Interview answer:** “Most aggregate functions ignore NULL. COUNT(*) counts rows, COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts unique non-NULL values.”

`IN` behaves like comparisons joined by `OR`:

```sql
3 IN (1, NULL)  -- FALSE OR UNKNOWN → UNKNOWN
1 IN (1, NULL)  -- TRUE OR UNKNOWN  → TRUE
```

`NOT IN` negates that result:

```sql
3 NOT IN (1, NULL) -- NOT UNKNOWN → UNKNOWN
1 NOT IN (1, NULL) -- NOT TRUE    → FALSE
```

**Pitfall:** If the subquery returns any `NULL`, `NOT IN` cannot evaluate to TRUE, so `WHERE` returns no rows.

Use `NOT EXISTS` to check for the absence of matching rows:

```sql
SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);
```

A `NULL` in `orders.user_id` does not match `u.id` and does not affect other comparisons.

**Interview answer:** “NULL can make IN and NOT IN evaluate to UNKNOWN. NOT EXISTS avoids this trap by checking whether matching rows exist, so nullable values in the subquery do not invalidate the result.”

| SQL construct | When the condition is UNKNOWN |
|---|---|
| `WHERE` | Excludes the row |
| `HAVING` | Excludes the group |
| `CHECK` | Accepts the value; rejects only FALSE |

```sql
age INT CHECK (age >= 18)
```

This constraint allows `NULL` because `NULL >= 18` evaluates to **UNKNOWN**. To reject missing values, add `NOT NULL`:

```sql
age INT NOT NULL CHECK (age >= 18)
```

**Interview answer:** “WHERE and HAVING keep only TRUE results. CHECK accepts TRUE or UNKNOWN and rejects only FALSE, so a CHECK constraint does not replace NOT NULL.”

`NULL` represents a missing or unknown value. Comparing anything to it using `=` or `<>` produces **UNKNOWN**, even `NULL = NULL`.

Since `WHERE` keeps only **TRUE** conditions, neither comparison selects rows.

Use these predicates instead:

```sql
WHERE column IS NULL      -- Select missing values
WHERE column IS NOT NULL  -- Select present values
```

**Interview answer:** “Use IS NULL and IS NOT NULL to check whether a value is missing. Ordinary comparisons with NULL return UNKNOWN, which WHERE filters out.”

SQL uses **three-valued logic** because comparisons involving `NULL` generally produce **UNKNOWN**: SQL cannot determine whether the condition is true or false.

```sql
5 = 5       -- TRUE
5 = 3       -- FALSE
5 = NULL    -- UNKNOWN
NULL = NULL -- UNKNOWN
```

### AND, OR, and NOT with UNKNOWN

| Expression | Result |
|---|---|
| `TRUE AND UNKNOWN` | `UNKNOWN` |
| `FALSE AND UNKNOWN` | `FALSE` |
| `UNKNOWN AND UNKNOWN` | `UNKNOWN` |
| `TRUE OR UNKNOWN` | `TRUE` |
| `FALSE OR UNKNOWN` | `UNKNOWN` |
| `UNKNOWN OR UNKNOWN` | `UNKNOWN` |
| `NOT UNKNOWN` | `UNKNOWN` |

Remember: **FALSE determines an AND; TRUE determines an OR.** Otherwise, uncertainty remains.

### Practical pitfall

`WHERE` keeps only rows where the condition is **TRUE**. Both `FALSE` and `UNKNOWN` are excluded.

```sql
SELECT * FROM users WHERE age <> 18;
-- Also excludes users whose age is NULL.
```

To include users with an unknown age:

```sql
SELECT * FROM users
WHERE age <> 18 OR age IS NULL;
```

### Interview answer

“SQL uses TRUE, FALSE, and UNKNOWN to handle missing values. FALSE AND UNKNOWN is FALSE, TRUE OR UNKNOWN is TRUE, and NOT UNKNOWN remains UNKNOWN. WHERE returns only rows whose condition evaluates to TRUE.”

filter in network

-status-code:200