Combine true/false with unknown values

In the beginner lesson, you learned that BOOLEAN stores TRUE or FALSE. But SQL booleans have a critical third state: NULL. This is called three-valued logic, and it breaks many assumptions that work in normal programming.

TRUE

Confirmed positive. Stored as 1 byte (0x01).

FALSE

Confirmed negative. Stored as 1 byte (0x00).

NULL

Unknown or undefined. Not true, not false.

NULL propagates through logical expressions in ways that surprise most developers. Any operation involving NULL produces NULL, not TRUE or FALSE:

Different filtering styles handle NULL values differently. Choose the pattern that matches your intent.

Handling NULL correctly often pairs with type conversion. Try formatting a price column with exact decimal precision.

SELECT
  product_name,
  ___(price AS ___(10, 2)) AS formatted_price
FROM products

Three-valued logic also affects aggregations. COUNT(*) counts all rows including those with NULL values, but COUNT(column) skips NULL rows. This distinction changes results when NULL values are present.

The IS NULL and IS NOT NULL operators are the correct way to test for NULL. Using = NULL or != NULL always evaluates to NULL (unknown), causing those rows to be silently excluded from results.

Boolean filtering with WHERE is_active and WHERE is_active = TRUE behave identically for non-NULL values, but differ in clarity. Use whichever makes intent most obvious to the next reader.

Store exact numbers for financial data

Floating-point numbers (FLOAT, DOUBLE) approximate values using binary fractions. This causes the famous 0.1 + 0.2 problem: the result is 0.30000000000000004, not 0.3. For financial calculations, use DECIMAL instead. It stores numbers as exact base-10 digits.

SELECT
  order_id,
  ___(subtotal + tax AS ___(19, 4)) AS total
FROM orders

DECIMAL(19,4) is the industry standard for financial calculations, providing 4 decimal places for sub-cent precision.

Rounding errors from FLOAT may seem small per row, but they accumulate across thousands of transactions into real money discrepancies.

When migrating from FLOAT to DECIMAL, always verify totals match before and after conversion to catch any accumulated drift.

Track moments across global systems

TIMESTAMP for schedules

"Backup runs at 3am daily" stays 3am in every timezone.

WITH TIME ZONE for events

"User clicked buy at this exact moment" needs UTC precision.

DST transition danger

TIMESTAMP without timezone is ambiguous during DST: 2am happens twice.

Cross-timezone coordination

TIMESTAMP WITH TIME ZONE ensures "same moment" across all offices.

SELECT
  order_id,
  ___(created_at AS ___) AS order_date
FROM orders

Storing the full TIMESTAMP and truncating to DATE at query time is the best practice. This preserves detail for future analysis while still supporting daily grouping when needed.

TIMESTAMP WITH TIME ZONE is preferred for event data in global applications. It stores the absolute UTC moment and converts to the session timezone only when displaying results.

Plain TIMESTAMP without timezone information is appropriate for calendar-based data like scheduled reports, recurring reminders, or business-hours definitions that should not shift with timezone.

Standardize times for distributed data

SELECT
  event_id,
  ___ AT TIME ZONE ___ AS local_time
FROM event_data

Convert types without crashing on bad data

In the beginner lesson, you learned CAST for type conversion. But CAST crashes the entire query if any single value fails to convert. TRY_CAST is the production-safe alternative: it returns NULL instead of throwing an error.

Example: INTEGER + DECIMAL = DECIMAL, because DECIMAL can represent all integers exactly.

SELECT
  ___(kv_value AS ___)
FROM kv_store

TRY_CAST combined with COALESCE provides a complete safety net: TRY_CAST handles conversion failures and COALESCE substitutes a default value so downstream queries never encounter unexpected NULLs.

Implicit type coercion happens silently and can cause queries on indexed columns to fall back to full table scans. Explicit CAST makes intent clear and avoids unintended performance regressions.