SQL CASE, Functions and Calculated Columns

A result column can show a value that is not stored anywhere in the table. SQL calculates it from the current row. An expression combines columns, fixed values, operators, and function calls.

Calculate a value for each row

SELECT order_id,
       quantity * unit_price AS line_total
FROM orders;

For quantity 3 and unit price 12.50, the new column shows 37.5. Each row supplies its own quantity and price. The query leaves the stored columns unchanged. A single-row function, such as ROUND(unit_price, 0), also returns one value per input row. Adding it to the SELECT list does not combine rows. An aggregate function such as SUM or AVG instead calculates a value for a group.

The name after AS is a column alias: a heading for the result. Without it, SQLite uses the expression text as the heading; PostgreSQL uses ?column? for this arithmetic expression. AS is optional, but it makes a name easier to distinguish from a missing comma.

Choose names and quotes

Use a simple name such as line_total: letters, digits, and underscores, starting with a letter. A name containing a space or a reserved word needs double quotes: AS "Line Total" or AS "order". Single quotes mark text values, such as 'Boston'. MySQL also accepts backticks for names, and SQL Server accepts square brackets.

PostgreSQL folds unquoted names to lowercase: AS LineTotal produces linetotal. Double quotes keep the spelling in AS "LineTotal". SQLite keeps an alias as typed. Lowercase names with underscores work neatly in both.

Use an alias at the right stage

The logical order is FROM, WHERE, SELECT, then ORDER BY. The table is chosen and rows are filtered before output expressions receive their aliases. Sorting happens afterward.

SELECT order_id,
       quantity * unit_price AS line_total
FROM orders
WHERE quantity * unit_price > 100
ORDER BY line_total DESC;

ORDER BY can use line_total. Standard SQL, PostgreSQL, and MySQL cannot use that new alias in WHERE; repeat the expression there. SQLite accepts an alias in WHERE as an extension, but that form will fail when moved to PostgreSQL.

A new alias is also unavailable to another expression in the same SELECT list. After unit_price * 1.08 AS with_tax, writing with_tax - unit_price AS tax fails if the table has no with_tax column. Write unit_price * 1.08 - unit_price AS tax instead.

Diagnose a surprising result

SELECT first_name last_name FROM customers; is valid SQL. It displays one column of first names with the alias last_name. To retrieve both columns, write SELECT first_name, last_name FROM customers;. A missing comma can therefore give a misleading result without an error.

For a quick expression check, a table is unnecessary: SELECT 19.99 * 3 AS total; returns 59.97 in SQLite and PostgreSQL. Oracle before Release 23 requires FROM DUAL for this query; Release 23 and later also allow SELECT without FROM.

Arithmetic expressions turn raw quantities into amounts, rates, and differences. The operators are +, -, *, /, and %; modulo gives the remainder after division.

Control the calculation order

Multiplication, division, and modulo come before addition and subtraction. Equal-rank operators run left to right. Thus 10 + 3 * 2 gives 16, while (10 + 3) * 2 gives 26.

For quantity 3, unit price 12.50, and discount percentage 10:

quantity * unit_price * (1 - discount_pct / 100.0)

First 10 / 100.0 gives 0.1. The parentheses give the fraction paid, 0.9. The gross amount is 37.5, so the net amount is 33.75. Without parentheses, quantity * unit_price * 1 - discount_pct / 100.0 subtracts 0.1 from 37.5 and gives 37.4.

Keep fractions during division

In SQLite, PostgreSQL, and SQL Server, integer divided by integer drops the fraction toward zero: 7 / 2 gives 3, and -7 / 2 gives -3. MySQL and Oracle give a decimal answer for /. In SQLite and PostgreSQL, make an operand decimal before dividing: 7 / 2.0, 7 * 1.0 / 2, or CAST(7 AS REAL) / 2 gives 3.5. CAST(7 / 2 AS REAL) is too late and gives 3.0.

For a rise from 80 to 92, (92 - 80) * 100.0 / 80 gives 15.0 percent. Dividing the integer difference by 80 first loses the fraction. Modulo keeps the leftover part: 135 / 60 is 2 hours and 135 % 60 is 15 minutes. In SQLite and PostgreSQL, -7 % 3 gives -1; the remainder follows the left operand's sign. Oracle writes MOD(17, 5) instead of %.

A zero divisor raises an error in PostgreSQL and SQL Server; SQLite and MySQL return NULL. amount / NULLIF(divisor, 0) makes the zero case return NULL across these systems.

Round or choose a whole number

ROUND(x, n) rounds to n decimal places. A lamp priced at 24.00 with cost 15.50 has profit 8.5. ROUND((price - cost) / price * 100, 1) gives a margin of 35.4 percent. PostgreSQL supports negative digits: ROUND(1234.5678, -2) gives 1200; SQLite treats the digits as zero and gives 1235.0.

PostgreSQL provides these functions. SQLite provides ABS; CEIL, FLOOR, and TRUNC require a build with math functions, available from 3.35.

Match precision to the data

SQLite ROUND and PostgreSQL's numeric ROUND round exact halves away from zero: ROUND(-2.5) gives -3. PostgreSQL floating-point rounding usually takes ties to the nearest even integer; its two-argument ROUND requires numeric, so use ROUND(CAST(x AS numeric), 2) for a double-precision column.

Floating-point numbers approximate many decimal fractions. SQLite can make 0.1 + 0.2 = 0.3 false and ROUND(2.675, 2) return 2.67. PostgreSQL's decimal literals use exact numeric. For exact money, use PostgreSQL NUMERIC/DECIMAL or integer cents in SQLite. Declaring SQLite DECIMAL does not create exact decimal storage. Keep precision through calculations and round for display.

A value's data type affects arithmetic, comparisons, and sorting. The number 42 is different from the text '42', even when a result grid displays them alike.

Convert a value explicitly

Common types include INTEGER for whole numbers, NUMERIC or DECIMAL for exact decimals in PostgreSQL, REAL or double precision for approximate numbers, TEXT or VARCHAR for text, and DATE, TIME, TIMESTAMP, and BOOLEAN where supported.

SELECT CAST('42' AS INTEGER) AS number_value,
       CAST(42 AS TEXT) AS text_value;

CAST(expression AS type) is standard SQL. The first result is a number; the second is text. PostgreSQL also accepts '42'::integer. Casting NULL still gives NULL.

For decimal division in SQLite, write CAST(quantity AS REAL) / 4; in PostgreSQL, write CAST(quantity AS NUMERIC) / 4. Quantity 7 gives 1.75. SQLite's CAST(7 AS NUMERIC) / 2 still gives 3: NUMERIC leaves that whole value stored as an integer. REAL is the suitable SQLite cast for this purpose.

Know what an integer cast does

SQLite removes a fraction toward zero. PostgreSQL rounds a numeric value to the nearest integer. Use ROUND, TRUNC, FLOOR, or CEIL first when the intended rule matters. PostgreSQL rejects text that is not a valid number. SQLite reads a leading numeric part: '12abc' becomes 12 and '12.5' becomes 12 when cast to INTEGER. Zero after a SQLite cast therefore does not prove that the original text was a valid zero. SQL Server's TRY_CAST returns NULL on a failed conversion.

Separate quantities from identifiers

Text sorts character by character. The strings '9', '10', and '100' sort as '10', '100', '9'. To order genuine numeric quantities kept as text, use ORDER BY CAST(code AS INTEGER) after ensuring they are valid numbers.

A ZIP code such as '02134' is an identifier. Casting it gives 2134 and loses the first zero. Phone numbers also belong as text: their digits label something, rather than describe an amount to add or average.

Recognize automatic conversions

Both SQLite and PostgreSQL convert the number to text in 'Order #' || 42, producing 'Order #42'. Both also evaluate the quoted literal in '5' + 3 as a number. In PostgreSQL, that literal initially has an open type; a TEXT column does not have the same freedom. Adding 3 to a TEXT column fails, so cast the column explicitly.

SQLite gives columns a type preference, called type affinity, and can keep different value types in one ordinary column. typeof(42), typeof(4.2), typeof('42'), and typeof(NULL) report 'integer', 'real', 'text', and 'null'. SQLite has no dedicated DATE type: CAST('2026-03-14' AS DATE) gives 2026. Use date('2026-03-14') for its date functions.

Oracle provides TO_NUMBER, TO_CHAR, and TO_DATE. PostgreSQL's TO_CHAR and TO_DATE take format patterns such as 'MM/DD/YYYY'. SQL Server's CONVERT uses a numeric style code: CONVERT(date, '03/14/2026', 101) reads the US date layout.

NULL marks a missing or unknown value. A blank result cell might display NULL, but NULL is not an ordinary value that you compare with =.

Follow a missing input through an expression

Zero is a known number. Empty text, '', is a known string with no characters. The text 'N/A' is a recorded label. NULL says that no value is available. Oracle treats empty text as NULL; SQLite and PostgreSQL distinguish them.

Most arithmetic and functions pass a missing input on as NULL. With salary 50000 and bonus NULL, salary + bonus gives NULL. The total is unknown because the bonus is unknown. UPPER(NULL), ROUND(NULL, 2), and 'Ana ' || NULL also return NULL in SQLite and PostgreSQL.

Test for NULL directly

bonus = NULL, bonus <> NULL, and even NULL = NULL produce UNKNOWN. Two missing values are not known to be equal. Use bonus IS NULL or bonus IS NOT NULL. These tests always return TRUE or FALSE.

SQL conditions have three possible results: TRUE, FALSE, and UNKNOWN. SQLite displays TRUE as 1, FALSE as 0, and UNKNOWN as NULL.

Understand which rows WHERE keeps

WHERE keeps only TRUE. FALSE and UNKNOWN both leave the row out. For bonuses 1000, 0, NULL, 2500, and NULL, WHERE bonus < 1000 keeps only the zero. WHERE bonus >= 1000 keeps 1000 and 2500. These filters cover three rows, not five. WHERE NOT (bonus < 1000) also keeps only the two known bonuses at least 1000.

The same issue affects <>. With cities Boston, Denver, NULL, Boston, and Austin, WHERE city <> 'Boston' keeps Denver and Austin. Include the unknown city explicitly:

WHERE city <> 'Boston' OR city IS NULL

This keeps three rows. NOT does not automatically bring missing values back.

Watch lists and comparisons

3 IN (1, 2, NULL) is UNKNOWN: the missing member could be 3. For x NOT IN (1, 2, NULL), a matching value fails NOT IN and any unmatched value gives UNKNOWN. In a WHERE clause this fixed list keeps no rows. Remove an unintended NULL from an exclusion list before using it.

For equality that treats two NULLs as equal, use a IS NOT DISTINCT FROM b. Its opposite, a IS DISTINCT FROM b, is TRUE when one value is NULL and the other is not; two NULLs are not distinct. PostgreSQL and SQLite 3.39 onward support these forms. SQLite also has IS and IS NOT; MySQL writes NULL-safe equality as <=>.

NULL sorting defaults differ: SQLite puts NULL first in ascending order; PostgreSQL puts it last. State the placement when it matters: ORDER BY bonus DESC NULLS LAST puts known bonuses in descending order and missing bonuses at the end.

Missing values sometimes need a fallback, and sometimes should stay missing. The choice is part of what a report means. COALESCE fills a gap; NULLIF turns a matching placeholder into a gap.

Choose the first available value

COALESCE(a, b, ...) checks left to right and returns the first non-NULL argument. It returns NULL if every argument is NULL.

SELECT COALESCE(mobile_phone, home_phone, 'no phone') AS contact
FROM customers;

With no mobile number and home phone '555-0100', the contact is '555-0100'. If neither number is recorded, it is 'no phone'. A known zero or empty string counts as available: COALESCE skips NULL, not values that look blank or false.

For pay, salary + COALESCE(bonus, 0) gives 50000 when salary is 50000 and bonus is NULL. This is appropriate when a missing bonus means none was paid. A missing product rating does not mean a rating of zero; replacing it would invent a poor rating. Leave that number missing or create a text display label.

Keep fallback types compatible

PostgreSQL cannot combine integer rating with text 'not rated' in one numeric result. Convert the known ratings to text first:

COALESCE(CAST(rating AS TEXT), 'not rated')

A rating of 4 displays as text '4'; a NULL displays as 'not rated'. SQLite accepts mixed numeric and text arguments, but then the output can contain different types. Make the intended result type clear when moving a query between databases.

COALESCE is standard SQL and accepts two or more arguments. The common two-argument alternatives are IFNULL in SQLite and MySQL, ISNULL in SQL Server, and NVL in Oracle.

Turn a placeholder into NULL

NULLIF(a, b) returns NULL if the arguments are equal; otherwise it returns the first argument. NULLIF(0, 0) is NULL, while NULLIF(5, 0) is 5.

A useful rate expression is:

orders * 100.0 / NULLIF(visits, 0) AS conversion_pct

With 12 orders and 300 visits, the divisor stays 300 and the rate is 4.0. With zero visits, the divisor becomes NULL and the rate becomes NULL. PostgreSQL avoids a division-by-zero error, and the result correctly marks an undefined rate. Replacing that rate with zero would make a different claim.

Clean gaps before supplying a label

COALESCE(NULLIF(phone, ''), 'unknown')

The inner NULLIF turns an empty string into NULL. The outer COALESCE then labels either an original NULL or an empty string as 'unknown'. A recorded phone number passes through unchanged.

For an optional middle name, include its trailing space inside the fallback:

first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name

A NULL middle name contributes nothing, producing 'Ana Lopez'. A middle name of 'Maria' contributes 'Maria ', producing 'Ana Maria Lopez'.

These expressions change the query result only. They leave the original values stored in the table. A filter such as WHERE COALESCE(bonus, 0) = 0 groups known zero bonuses and missing bonuses together, so use it when those are the two groups you want.

SQL treats a text value as a string of characters. Text functions can build a full name or tidy an email address for comparison. Their results appear in the query; stored text stays unchanged.

Write and join text

Text literals use single quotes. To include an apostrophe, double it: 'O''Brien' represents "O'Brien". A pair of single quotes, '', is empty text.

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

The concatenation operator || joins the pieces exactly as written. With 'Ana' and 'Lopez', the result is 'Ana Lopez'. The middle literal supplies the space; concatenation does not insert one automatically.

SQLite, PostgreSQL, and Oracle use ||. MySQL's default || means OR, so use CONCAT there. SQL Server uses + and also supports || from 2025. To join a numeric order ID, SQLite and PostgreSQL convert it to text in 'Order #' || order_id. With SQL Server's +, cast the number first: 'Order #' + CAST(order_id AS VARCHAR(10)).

Choose how to join a missing part

In SQLite and PostgreSQL, a NULL operand makes a || result NULL. Oracle treats that missing part as empty text. CONCAT also differs by database:

Oracle's CONCAT takes exactly two arguments. CONCAT_WS(' ', first_name, middle_name, last_name) places spaces between non-NULL parts, so a missing middle name gives 'Ana Lopez'. SQLite added CONCAT and CONCAT_WS in 3.44. Skipping NULL still does not remove an empty string.

Change case and trim edges

UPPER('boston') gives 'BOSTON'; LOWER produces lowercase. PostgreSQL's INITCAP('ana LOPEZ') gives 'Ana Lopez'. SQLite has no INITCAP, and its built-in case conversion changes A–Z only: UPPER('é') stays 'é' without the ICU extension. PostgreSQL case conversion follows its locale.

TRIM removes spaces at both ends; LTRIM removes them only at the start and RTRIM only at the end. TRIM(' New York ') gives 'New York', keeping the two spaces inside. To remove x from both ends, SQLite writes TRIM('xxBostonxx', 'x'); PostgreSQL writes TRIM(BOTH 'x' FROM 'xxBostonxx').

Replace characters and nest cleaning steps

REPLACE('555-0100', '-', '') gives '5550100'. It replaces every exact occurrence. Letter case matters: replacing 'st' in 'Main St' changes nothing because 'St' has a capital S.

SELECT LOWER(TRIM(email)) AS email_clean
FROM customers;

For ' Ana@Example.COM ', TRIM first produces 'Ana@Example.COM', then LOWER produces 'ana@example.com'. Nesting means the innermost function supplies the input of the next.

Ordinary text equality is case-sensitive by default in SQLite and PostgreSQL. WHERE LOWER(TRIM(email)) = 'ana@example.com' can match differently capitalized input with edge spaces. MySQL's default collation ignores case, and SQLite LIKE ignores A–Z case even though its default = comparison does not.

Text functions locate and cut pieces by character position. SQL starts positions at 1, so the first character is position 1. A found position and a number of characters are different inputs.

Measure and cut a string

LENGTH('Boston') gives 6. Spaces count: LENGTH(' Bo ') gives 4. Empty text has length 0; LENGTH(NULL) is NULL. MySQL's LENGTH counts bytes, so use CHAR_LENGTH for characters there. SQL Server uses LEN, which ignores trailing spaces.

SELECT SUBSTR('Boston', 1, 3) AS beginning,
       SUBSTR('Boston', 4) AS ending;

The results are 'Bos' and 'ton'. The first call takes three characters starting at position 1. The second omits the count and takes everything from position 4 onward. PostgreSQL also writes SUBSTRING('Boston' FROM 4 FOR 3). SQLite accepts SUBSTRING as a name from 3.34. SQL Server uses SUBSTRING(text, start, count); the count is required through 2022 and optional from 2025.

SQLite can count from the right: SUBSTR('Boston', -3) gives 'ton'. PostgreSQL negative starts do not count backward from the end. Use RIGHT('Boston', 3) there; LEFT takes characters from the beginning. SQLite has neither LEFT nor RIGHT.

Find the split point

SQLite's INSTR(text, search) returns the first matching position. PostgreSQL uses STRPOS or POSITION(search IN text). These functions return 0 when no match is found. The search is case-sensitive: INSTR('Ana', 'a') is 3.

For 'ana@example.com', the @ is at position 4. The user name ends just before it; the domain starts just after it:

SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_name,
SUBSTR(email, INSTR(email, '@') + 1) AS domain

The first expression becomes SUBSTR(email, 1, 3) and yields 'ana'. The second starts at 5 and yields 'example.com'. Omitting - 1 includes @ in the user name. Omitting + 1 includes it at the start of the domain. In PostgreSQL, substitute STRPOS for INSTR, or use SPLIT_PART(email, '@', 2) for the domain.

Handle a missing separator

For 'anaexample.com', INSTR returns 0. The domain formula starts at 1 and misleadingly returns the whole string. SQLite's user-name formula gives empty text; PostgreSQL rejects its negative substring length. Guard the extraction:

CASE
  WHEN INSTR(email, '@') > 0
  THEN SUBSTR(email, INSTR(email, '@') + 1)
END

This returns NULL when the separator is absent. The guard checks for a separator, not every rule of a valid email address.

Build short labels and fixed-width codes

SUBSTR(first_name, 1, 1) || '. ' || last_name gives 'A. Lopez' for Ana Lopez. WHERE LENGTH(zip_code) <> 5 finds recorded ZIP strings of the wrong length; NULL lengths produce UNKNOWN and are left out.

PostgreSQL's LPAD('42', 5, '0') gives '00042'; RPAD pads on the right. SQLite can build that code with SUBSTR('00000' || 42, -5). Keeping only five characters also cuts off a longer value, so this fixed-width expression needs a code that fits.

A date is easier to calculate with when its storage and spelling are clear. The text '03/04/2026' could mean March 4 or April 3. An ISO date, '2026-03-14', puts year, month, and day in an unambiguous order.

Match the value to its type

PostgreSQL has DATE for a calendar day, TIME for a time of day, and TIMESTAMP for a date and time. Its TIMESTAMP WITH TIME ZONE, also called timestamptz, stores a moment in UTC and displays it in the session's time zone. A plain TIMESTAMP does not attach that zone meaning.

SQLite stores dates and times as ISO text or as numbers, not dedicated date/time types. Numeric forms include Julian day numbers and Unix time. The date functions interpret these values and return a date, time, or numeric measure.

Use fixed-width ISO text such as '2026-03-14', including leading zeros. Add a time using the 24-hour clock: '2026-03-14 09:30:00'. PostgreSQL's typed literal DATE '2026-03-14' explicitly creates a date.

Preserve chronological order

Fixed-width ISO text compares in date order because it starts with the largest part. '2026-03-14' comes before '2026-11-02'. US display text does not have this property: as strings, '11/02/2025' sorts after '03/14/2026' even though its date is earlier.

SQLite's parser needs the supported layout. date('2026-3-14') returns NULL because the month is not two digits. It can also normalize an impossible date: date('2026-02-30') returns '2026-03-02'. A non-NULL result alone is not proof that the input was a valid calendar day. PostgreSQL rejects DATE '2026-02-30'.

Read text in another layout

PostgreSQL and Oracle use TO_DATE('03/14/2026', 'MM/DD/YYYY') to read a US layout. PostgreSQL also reads ISO text with CAST('2026-03-14' AS DATE) or '2026-03-14'::date.

SQLite has no TO_DATE parser. For fixed MM/DD/YYYY text in d, rebuild ISO text:

SUBSTR(d, 7, 4) || '-' ||
SUBSTR(d, 1, 2) || '-' || SUBSTR(d, 4, 2)

The pieces are the year '2026', month '03', and day '14'. The result is '2026-03-14', which SQLite's date functions can read. The positions rely on the input actually using the fixed layout.

Get the current date and time

CURRENT_DATE and CURRENT_TIMESTAMP work in SQLite and PostgreSQL. SQLite returns UTC text; date('now', 'localtime') gives the local date. Near midnight, UTC and the local calendar date can differ.

PostgreSQL uses the session's time zone. CURRENT_TIMESTAMP and NOW() reflect the start of the current transaction, so waiting inside that transaction does not advance their value. MySQL offers NOW() and CURDATE(); SQL Server has GETDATE() and SYSDATETIME(); Oracle has SYSDATE, which includes a time, and CURRENT_DATE.

To check a due-date calculation, first use a fixed anchor such as DATE '2026-10-21' in PostgreSQL or '2026-10-21' in SQLite. You can then compare the result with a calendar without the answer changing the next day.

Extract a date part when you need a year or weekday to calculate with. Format a date when you need text for a reader. These operations can produce different types even when the displayed characters look alike.

Extract a year, month, or weekday

PostgreSQL uses standard EXTRACT(part FROM value). For DATE '2026-03-14', extracting YEAR gives 2026 and MONTH gives 3, both as numbers. MySQL and Oracle also support EXTRACT.

SQLite uses strftime(format, value), which returns text:

SQLite's %w and PostgreSQL's DOW number Sunday as 0 through Saturday as 6. March 14, 2026, is Saturday. PostgreSQL ISODOW instead numbers Monday as 1 through Sunday as 7. A SQLite weekend filter is WHERE strftime('%w', order_date) IN ('0', '6').

Compare the right types

strftime('%m', order_date) = 3 is false for a March date in SQLite: the function returns text '03', not number 3. Compare it with = '03', or convert first:

CAST(strftime('%m', order_date) AS INTEGER) = 3

This is also useful in the quarter expression (CAST(strftime('%m', d) AS INTEGER) + 2) / 3. Integer division gives quarter 1 for March and quarter 4 for November. PostgreSQL has EXTRACT(QUARTER FROM d) directly.

Identify a month as a whole period

strftime('%Y-%m', order_date) creates a SQLite label such as '2026-03'. date(order_date, 'start of month') creates '2026-03-01'. PostgreSQL can use TO_CHAR(order_date, 'YYYY-MM') for a label or DATE_TRUNC('month', order_date) for the first moment of that month.

Keep the year in a period label. A bare month value of 3 would combine March from different years. The first-of-month value can remain the calculation and sorting value while a separate expression supplies the display.

Format at the output stage

In PostgreSQL, TO_CHAR(DATE '2026-03-14', 'MM/DD/YYYY') gives '03/14/2026', and the pattern 'Mon DD, YYYY' gives 'Mar 14, 2026'. Oracle uses these patterns too. SQLite's US layout is strftime('%m/%d/%Y', '2026-03-14').

Pattern letters belong to their database. PostgreSQL MM means month and MI means minute. SQLite %m means month and %M means minute; changing that capital changes the information requested.

SELECT TO_CHAR(order_date, 'MM/DD/YYYY') AS order_day
FROM orders
ORDER BY order_date;

Here PostgreSQL displays readable text but sorts the actual dates. Sorting order_day would sort the display strings by month first and could put dates from different years out of order.

Calendar arithmetic must account for month lengths, time components, and the kind of answer needed. A number of days, a calendar month, and a whole year of age are different measures.

Add days and calendar months

PostgreSQL writes DATE '2026-03-14' + 30; SQLite writes date('2026-03-14', '+30 days'). Both reach April 13: 17 days finish March and 13 more reach April 13. SQLite's direct expression '2026-03-14' + 30 instead gives 2056, because arithmetic reads the leading year as a number.

Month ends need an explicit policy. PostgreSQL's DATE '2026-01-31' + INTERVAL '1 month' gives February 28 at midnight, using the last available day. SQLite's date('2026-01-31', '+1 month') rolls excess days into March and gives '2026-03-03'. From SQLite 3.46, add 'floor' after the shift to choose '2026-02-28'. Oracle ADD_MONTHS also moves to the shorter month's last day.

Measure elapsed time

PostgreSQL DATE subtraction returns whole days: DATE '2026-04-13' - DATE '2026-03-14' is 30. SQLite subtracts numeric day values: julianday('2026-04-13') - julianday('2026-03-14') is 30.0. SQL Server uses DATEDIFF(day, start, end); MySQL uses DATEDIFF(end, start).

For timestamps from March 14 at 09:30 to 18:00, PostgreSQL subtraction gives an interval of 08:30:00; EXTRACT(EPOCH FROM interval) / 3600 expresses it as 8.5 hours. SQLite 3.38 onward can write:

(unixepoch('2026-03-14 18:00') -
 unixepoch('2026-03-14 09:30')) / 3600.0

This gives 8.5. A Julian-day difference multiplied by 24 can give 8.49999999627471 because of floating-point approximation; round the display. A fractional timestamp duration should not be cast to INTEGER before the desired unit is calculated.

Count completed birthdays

A person born November 2, 1990, is 35 on October 21, 2026. The year numbers differ by 36, but the birthday has not arrived. PostgreSQL uses EXTRACT(YEAR FROM AGE(DATE '2026-10-21', DATE '1990-11-02')), giving 35.

SQLite subtracts one if this year's birthday is still ahead:

strftime('%Y', '2026-10-21') - strftime('%Y', birth_date)
- (strftime('%m-%d', '2026-10-21') < strftime('%m-%d', birth_date))

The comparison yields 1 when the birthday is ahead and 0 otherwise. Dividing elapsed days by 365 is unreliable: on November 1, 2026, this person has lived 13148 days, which divided by 365 gives 36.02 even though the 36th birthday is tomorrow. SQL Server DATEDIFF counts boundaries: crossing December 31 to January 1 counts as a year boundary, not a completed year of age.

Include every timestamp in a period

BETWEEN includes both endpoints. For DATE columns, March 1 through March 31 covers March. For timestamps, an upper bound of '2026-03-31' means midnight at the start of the last day and misses the rest of it.

WHERE ordered_at >= '2026-03-01'
  AND ordered_at < '2026-04-01'

This half-open range includes the start and excludes the next period's start, covering every March time without choosing a final fraction of a second.

Overdue dates satisfy due_date < CURRENT_DATE. PostgreSQL's last-30-days lower bound is CURRENT_DATE - 30; SQLite's is date('now', '-30 days'). Days until a deadline are due_date - CURRENT_DATE for PostgreSQL DATE values and julianday(due_date) - julianday(date('now')) in SQLite.

A CASE expression chooses one result for each row. You can use it to label a price band, calculate a fee, or mark a record for attention.

Read the searched form

CASE
  WHEN total < 50 THEN 'small'
  WHEN total < 200 THEN 'medium'
  ELSE 'large'
END AS size_band

Each WHEN supplies a condition, and THEN supplies the value to return if that condition is TRUE. The database checks from top to bottom and takes the first true condition. END closes the expression; an alias can follow it. If no condition is TRUE and ELSE is absent, the result is NULL.

A total of 35 meets the first condition and becomes 'small'. A total of 50 fails the first but meets the second, becoming 'medium'. The same is true for 199.99. A total of 200 reaches ELSE and becomes 'large'.

Make order and boundaries match the rule

The second branch needs no total >= 50: a value below 50 has already returned from the first branch. Ascending < thresholds work because earlier branches remove the smaller values. Descending >= thresholds can work for the same reason.

Putting WHEN total < 200 first makes a later < 50 branch unreachable. A total of 35 already satisfies < 200, so it stops there. CASE does not choose the most specific condition or check which label sounds best.

Compare the business rule with each endpoint. < 50 places exactly 50 in the medium band; <= 50 places it in the small band. Check 49.99, 50, 199.99, and 200 to see both sides and the boundaries. If conditions are written as explicit intervals, make their endpoints fit without a gap or unintended overlap.

Handle NULL before ordinary ranges

A NULL total makes both comparisons UNKNOWN. In the first expression it therefore reaches ELSE and becomes 'large', which is misleading. Catch missingness first:

CASE
  WHEN total IS NULL THEN 'unknown'
  WHEN total < 50 THEN 'small'
  WHEN total < 200 THEN 'medium'
  ELSE 'large'
END

ELSE means "no condition was true," so it can catch missing inputs as well as known values outside the tested ranges.

Return a consistent kind of value

PostgreSQL needs compatible THEN and ELSE types. CASE WHEN quantity > 0 THEN unit_price ELSE 'n/a' END fails if unit_price is numeric, because 'n/a' cannot become a number. Use NULL for an unavailable numeric result, or cast the price to text if the whole output is a display label. SQLite permits mixed result types, which can complicate later calculations.

A condition can combine columns:

CASE
  WHEN country = 'US' AND subtotal >= 100 THEN 0
  ELSE 7.99
END AS shipping_fee

The returned fee can appear inside subtotal + CASE ... END AS amount_due. A CASE is a value expression, not a command that edits several columns; each result column needs its own expression.

CASE can also guard a row-level division: CASE WHEN visits = 0 THEN NULL ELSE orders * 100.0 / visits END. A zero row selects the NULL branch. orders * 100.0 / NULLIF(visits, 0) expresses the same missing-rate rule more briefly.

The simple CASE form translates one value into another. Searched CASE tests conditions; simple CASE compares the same expression with a series of values.

Translate codes with simple CASE

CASE status
  WHEN 'S' THEN 'Shipped'
  WHEN 'P' THEN 'Pending'
  WHEN 'C' THEN 'Canceled'
  ELSE 'Other'
END AS status_label

The implied tests are status = 'S', then status = 'P', then status = 'C'. A status outside these codes gets 'Other'. The searched form can write the same equality tests explicitly. Choose searched CASE for ranges, LIKE patterns, NULL tests, or conditions across several columns.

CASE region WHEN NULL THEN 'missing' ELSE region END cannot catch a NULL region. Its implied region = NULL is UNKNOWN, so the row reaches ELSE and remains NULL. Write CASE WHEN region IS NULL THEN 'missing' ELSE region END.

Assign a custom sort rank

Alphabetical priority order is high, low, medium. To sort by urgency, translate the labels to numbers:

SELECT ticket_id, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'high' THEN 1
           WHEN 'medium' THEN 2
           WHEN 'low' THEN 3
           ELSE 4
         END;

Ascending order now places high before medium before low, with other or missing values last. The CASE is a sort expression; it need not be a displayed column. Using DESC would reverse the numeric ranking.

Create a numeric flag

CASE WHEN discount_pct > 0 THEN 1 ELSE 0 END AS is_discounted

A positive percentage gets 1; a known zero gets 0. A NULL percentage also gets 0 because > 0 is UNKNOWN and ELSE catches it. This flag therefore means "a positive discount is recorded." It does not preserve the distinction between no discount and an unknown discount. If that distinction is needed, add an IS NULL branch with its own result.

Flags can be selected, sorted, filtered, or included in arithmetic. In a portable WHERE clause, repeat the expression or use its original condition instead of referring to a newly defined output alias.

Recognize related conditional functions

COALESCE(bonus, 0) is a short form of CASE WHEN bonus IS NOT NULL THEN bonus ELSE 0 END. NULLIF(a, b) corresponds to CASE WHEN a = b THEN NULL ELSE a END. Their names communicate these common purposes directly.

SQLite from 3.32 and SQL Server have IIF(condition, true_value, false_value); MySQL has IF. Oracle DECODE translates equality matches, for example DECODE(status, 'S', 'Shipped', 'P', 'Pending', 'Other'). DECODE treats two NULLs as equal, unlike the equality comparisons in simple CASE. Standard CASE works across these systems.

A CASE can contain another CASE, but combined conditions often make a flat expression clearer. A shipping rule can directly say WHEN country = 'US' AND subtotal >= 100 THEN 0, keeping the country and subtotal tests together.

A report can clean names, calculate net totals, identify months, and label shipping speed in one SELECT. Build each expression from its inputs and check the result before combining it with the others.

Work from known rows

An orders table contains these records. Dates are ISO text for the SQLite query below; quantity is an integer and unit_price is numeric.

Here a missing discount means no discount. Start by retrieving the raw fields. Add one calculated column at a time and use LIMIT 5 while checking a small sample.

Trace a nested calculation

ROUND(quantity * unit_price *
      (1 - COALESCE(discount_pct, 0) / 100.0), 2)

COALESCE provides a known discount first. Dividing by 100.0 preserves its fraction. Subtracting from 1 gives the fraction paid, multiplication gives the net amount, and ROUND sets the final precision. Ana's amount is 33.75, Ben's is 240.0, and Chen's is 51.0. Chen's NULL discount becomes zero; a missing shipping date, by contrast, should stay missing in the day count.

Assemble the SQLite report

SELECT order_id,
       first_name || ' ' || last_name AS customer,
       ROUND(quantity * unit_price *
         (1 - COALESCE(discount_pct, 0) / 100.0), 2) AS net_total,
       strftime('%Y-%m', order_date) AS order_month,
       CAST(julianday(shipped_date) - julianday(order_date)
         AS INTEGER) AS days_to_ship,
       CASE
         WHEN shipped_date IS NULL THEN 'not shipped'
         -- fast = shipped within 2 days
         WHEN julianday(shipped_date) - julianday(order_date) <= 2
           THEN 'fast'
         ELSE 'standard'
       END AS shipping
FROM orders
ORDER BY order_id;

Each expression has a clear output alias. The numeric day difference is cast to INTEGER because these are date-only values. The IS NULL branch comes before the speed comparison, so an unshipped order receives a useful label.

The output is:

Adapt types and query clauses

For PostgreSQL DATE columns, use TO_CHAR(order_date, 'YYYY-MM') for the month and shipped_date - order_date for the day count and CASE comparison. Keep unit_price as exact numeric for two-argument ROUND. The concatenation, discount fallback, and classification rule keep the same meaning.

To filter for rounded totals above 50 in both databases, repeat the full net expression in WHERE. Orders 1002 and 1003 remain. SQLite also permits WHERE net_total > 50, but PostgreSQL cannot see that new alias at the filtering stage. ORDER BY net_total DESC works in both.

When moving a report, check the types and function names in each expression. A text function must receive text, a date calculation must receive a supported date value, and a numeric fallback must express the intended rule. Keep each output on its own line; a short comment can define a business threshold without obscuring the SQL.