Data Types and NULL

Data types and NULL are where SQL stops feeling like a spreadsheet. A column type is a promise about meaning, operations, and storage. NULL is the database’s marker for absence, unknown, or not applicable. This lesson teaches you to choose types deliberately, model money and dates safely, understand SQLite’s flexible typing, and write queries that handle missing values without lying.

A data type is a promise about meaning

Start with type families, then verify your engine

Every database engine has its own exact type system, but most schema decisions start with a small set of value families. Text stores names, emails, codes, descriptions, and identifiers that are not meant for arithmetic. Integers store whole-number counts and stable identifiers. Decimal or exact numeric types store quantities that need fixed precision. Date/time types store moments, dates, or durations. Boolean values store yes/no states, even when the engine represents them internally as numbers. Binary types store bytes, not display text.

SQLite, which powers the browser runner, uses type affinity. A column declaration guides storage and conversion, but SQLite is more flexible than PostgreSQL or MySQL. That makes it excellent for learning SQL concepts in the browser, but it also means you must verify production behavior in the DBMS you deploy. A PostgreSQL integer column, a MySQL DECIMAL, and a SQLite NUMERIC column are related ideas, not identical contracts.

Run SQLite affinity and inspect storage classes

SQLite stores values using storage classes such as integer, real, text, blob, and null. Column declarations give SQLite an affinity, or preference, for how values should be stored. The typeof() function lets you inspect what actually happened. This matters because the same literal can behave differently when placed into columns with different affinities.

CREATE TABLE imported_values (
  value_id INTEGER PRIMARY KEY,
  text_value TEXT,
  integer_value INTEGER,
  real_value REAL,
  numeric_value NUMERIC
);

INSERT INTO imported_values
  (value_id, text_value, integer_value, real_value, numeric_value)
VALUES
  (1, '42', 42, 42.0, 42),
  (2, '79.99', 79, 79.99, 79.99),
  (3, '2026-09-13', 20260913, 2026.0913, '2026-09-13');

SELECT
  value_id,
  text_value,
  typeof(text_value) AS text_storage,
  typeof(integer_value) AS integer_storage,
  typeof(real_value) AS real_storage,
  typeof(numeric_value) AS numeric_storage
FROM imported_values
ORDER BY value_id;

Edit the query, predict the rows it will return, then run it.

Choose numeric types by the risk of being wrong

A numeric column is not just “anything with digits.” An identifier such as postal_code can contain leading zeroes and should often be text. A count of seats is an integer. A currency amount needs exact handling. A scientific measurement may tolerate approximate floating-point math. Choosing the wrong representation can create subtle defects: postal codes lose leading zeroes, prices gain rounding errors, and averages accidentally ignore missing facts.

For portable beginner projects, storing money as integer cents is often clear and reliable. Production billing systems also need currency, tax, rounding, refund, discount, and audit semantics. The schema should make those responsibilities explicit instead of pretending every price is just a display string.

CREATE TABLE subscriptions (
  subscription_id INTEGER PRIMARY KEY,
  plan_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  currency TEXT NOT NULL DEFAULT 'USD' CHECK (length(currency) = 3),
  active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))
);

INSERT INTO subscriptions
  (subscription_id, plan_name, price_cents, currency, active)
VALUES
  (1, 'Plus monthly', 1900, 'USD', 1),
  (2, 'Plus yearly', 19000, 'USD', 1),
  (3, 'Legacy trial', 0, 'USD', 0);

SELECT
  plan_name,
  '$' || printf('%.2f', price_cents / 100.0) AS display_price,
  CASE active WHEN 1 THEN 'active' ELSE 'inactive' END AS status
FROM subscriptions
ORDER BY price_cents;

Edit the query, predict the rows it will return, then run it.

Text, dates, and booleans need conventions

Text columns should store text facts, not every fact that arrived from a form as a string. Use text for names, emails, descriptions, tags, URLs, slugs, and domain codes. Use date/time representations when the value participates in time comparisons. In SQLite, ISO-like text timestamps such as YYYY-MM-DD HH:MM:SS sort usefully as text and can be passed to date/time functions. In PostgreSQL and other stricter systems, prefer native date and timestamp types.

SQLite does not have a separate boolean storage class. A common convention is INTEGER NOT NULL CHECK (flag IN (0, 1)). PostgreSQL has a real boolean type. MySQL has aliases and conventions. The point is the same: encode the intended states and reject impossible ones.

CREATE TABLE events (
  event_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  starts_at TEXT NOT NULL,
  ends_at TEXT,
  CHECK (ends_at IS NULL OR ends_at >= starts_at)
);

INSERT INTO events (event_id, title, starts_at, ends_at) VALUES
  (1, 'SQL study sprint', '2026-09-13 09:00:00', '2026-09-13 10:30:00'),
  (2, 'Open office hours', '2026-09-14 15:00:00', NULL),
  (3, 'Schema review', '2026-09-15 11:00:00', '2026-09-15 12:00:00');

SELECT
  title,
  date(starts_at) AS event_day,
  time(starts_at) AS start_time,
  COALESCE(time(ends_at), 'not scheduled') AS end_time
FROM events
WHERE starts_at >= '2026-09-14'
ORDER BY starts_at;

Edit the query, predict the rows it will return, then run it.

NULL is not zero, false, or an empty string

NULL means the value is absent, unknown, or not applicable. It is a marker, not a normal value. Zero means a known number with value 0. An empty string means a known text value with length 0. False means a known negative boolean state. These differences matter because users, reports, constraints, and application behavior depend on them.

Consider shipped_at. A row with NULL shipped time may mean the order has not shipped or the time is unknown. A row with an empty string usually means bad imported text that should be cleaned. A row with a real date means the shipment event is known. The query below shows how IS NULL and = '' answer different questions.

CREATE TABLE shipments (
  shipment_id INTEGER PRIMARY KEY,
  order_number TEXT NOT NULL UNIQUE,
  shipped_at TEXT,
  tracking_code TEXT
);

INSERT INTO shipments
  (shipment_id, order_number, shipped_at, tracking_code)
VALUES
  (1, 'SO-1001', '2026-09-10', 'TRK-001'),
  (2, 'SO-1002', NULL, NULL),
  (3, 'SO-1003', '', ''),
  (4, 'SO-1004', '2026-09-12', NULL);

SELECT order_number, shipped_at, tracking_code
FROM shipments
WHERE shipped_at IS NULL
ORDER BY shipment_id;

SELECT order_number, shipped_at, tracking_code
FROM shipments
WHERE shipped_at = ''
ORDER BY shipment_id;

Edit the query, predict the rows it will return, then run it.

Three-valued logic changes WHERE results

SQL comparisons do not return only true or false. When a comparison involves NULL, the result is usually unknown. WHERE keeps rows where the condition is true. It filters out false and unknown. That is why score >= 70 excludes both a score of 0 and a missing score, even though those rows mean different things.

CREATE TABLE quiz_attempts (
  attempt_id INTEGER PRIMARY KEY,
  student TEXT NOT NULL,
  score INTEGER CHECK (score BETWEEN 0 AND 100)
);

INSERT INTO quiz_attempts (attempt_id, student, score) VALUES
  (1, 'Ada', 92),
  (2, 'Grace', NULL),
  (3, 'Linus', 0),
  (4, 'Nora', 74);

SELECT student, score, score >= 70 AS comparison_result
FROM quiz_attempts
ORDER BY attempt_id;

SELECT student, score
FROM quiz_attempts
WHERE score >= 70
ORDER BY student;

SELECT student, score
FROM quiz_attempts
WHERE score IS NULL OR score < 70
ORDER BY student;

Edit the query, predict the rows it will return, then run it.

Use COALESCE and aggregates with intention

COALESCE returns the first non-NULL expression. It is useful for display fallbacks, grouping labels, and optional values in reports. Use it carefully: replacing a missing numeric value with zero can be honest for “no discount applied,” but dishonest for “score not graded yet.” A fallback in the SELECT list changes the result presentation; it does not repair stored data.

Aggregates also treat NULL deliberately. COUNT(*) counts rows. COUNT(column) counts known values in that column. SUM and AVG ignore NULL inputs. That behavior is useful, but it means every report should state whether it is counting records, known values, or missing values.

CREATE TABLE support_tickets (
  ticket_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  resolved_minutes INTEGER
);

INSERT INTO support_tickets (ticket_id, title, resolved_minutes) VALUES
  (1, 'Password reset', 12),
  (2, 'Billing question', NULL),
  (3, 'Export request', 35),
  (4, 'Login bug', NULL);

SELECT
  COUNT(*) AS total_tickets,
  COUNT(resolved_minutes) AS resolved_tickets,
  COUNT(*) - COUNT(resolved_minutes) AS unresolved_tickets,
  ROUND(AVG(resolved_minutes), 1) AS avg_minutes_for_resolved
FROM support_tickets;

Edit the query, predict the rows it will return, then run it.

Make required, optional, default, and unknown explicit

A column should allow NULL only when the model has a real missing state. Required identifiers, names, and state fields often deserve NOT NULL. Optional timestamps, completion dates, cancellation reasons, and external references may be nullable because the event has not happened or the value is not applicable. Defaults are powerful, but they should represent a true default rather than hiding incomplete writes.

Before you create a column, ask four questions: What does the value mean? Which operations must work correctly? Can the value be absent in a valid row? If it is absent, is that unknown, not applicable, not happened yet, or bad imported data? Good schemas are full of these small honest decisions.

Independent lab: repair a messy import

Real data often arrives as text, even when the facts are not text. Your job is to turn the imported members into a cleaner result without pretending bad data is good data. Convert blank strings into NULL, cast numeric ages only when they look numeric, map yes/no marketing flags to 1 and 0, and keep uncertain facts as NULL for later review.

CREATE TABLE raw_members (
  member_id INTEGER PRIMARY KEY,
  email TEXT,
  joined_at TEXT,
  age_text TEXT,
  marketing_opt_in TEXT
);

INSERT INTO raw_members
  (member_id, email, joined_at, age_text, marketing_opt_in)
VALUES
  (1, 'ada@example.test', '2026-09-01', '34', 'yes'),
  (2, '', '2026-09-03', '', 'no'),
  (3, 'linus@example.test', '', '29', ''),
  (4, 'grace@example.test', '2026-09-05', 'not supplied', 'yes');

SELECT
  member_id,
  NULLIF(email, '') AS email,
  NULLIF(joined_at, '') AS joined_at,
  CASE
    WHEN age_text GLOB '[0-9]*' AND age_text <> '' THEN CAST(age_text AS INTEGER)
    ELSE NULL
  END AS age,
  CASE marketing_opt_in
    WHEN 'yes' THEN 1
    WHEN 'no' THEN 0
    ELSE NULL
  END AS marketing_opt_in
FROM raw_members
ORDER BY member_id;

Edit the query, predict the rows it will return, then run it.

Lesson review

Types are part of the schema’s meaning, not decoration. Choose text, integer, exact numeric, date/time, boolean, and binary representations based on the fact being modeled and the operations required. SQLite’s affinity model is flexible, so inspect behavior while learning and verify stricter production engines separately. NULL is a missingness marker, distinct from zero, false, and empty strings. Comparisons with NULL produce unknown, WHERE keeps only true rows, and aggregates count rows versus known values differently. The best schemas make absence honest and visible.

• I can explain why a column type is a contract about meaning and operations.
• I can choose sensible representations for money, dates, booleans, identifiers, and display text.
• I can distinguish NULL, zero, false, and an empty string in both schema design and query filters.
• I can use IS NULL, IS NOT NULL, NULLIF, and COALESCE deliberately.
• I can predict why a nullable value may disappear from a WHERE result.
• I can write reports that separate total rows, known values, and missing values.