SQL Dialects in Interviews: PostgreSQL vs MySQL vs SQL Server

SQL interviews often test concepts that transfer across databases, but the query still has to parse. PostgreSQL, MySQL, and SQL Server agree on joins, grouping, conditional aggregation, common window functions, and much of standard SQL. They diverge most visibly around row limits, dates, text matching, booleans, and convenience functions.

The safest opening is simple: ask which database and version the interviewer expects. If the answer is “any dialect,” state yours before writing: “I’ll use PostgreSQL syntax, and I can translate any dialect-specific date functions.” That removes ambiguity without turning the interview into a standards debate.

If you inherit starter code, treat it as a dialect clue. Square-bracket identifiers and TOP suggest SQL Server; backticks and DATE_ADD suggest MySQL; ::, ILIKE, and date_trunc suggest PostgreSQL. Confirm rather than assume when the environment matters.

A practical dialect crosswalk

Limit rows and paginate

PostgreSQL and MySQL: put LIMIT after ORDER BY. Both accept the explicit offset form shown here.

SELECT customer_id, revenue
FROM customer_sales
ORDER BY revenue DESC, customer_id
LIMIT 10 OFFSET 20;

SQL Server: use TOP for a first page, or OFFSET and FETCH for pagination.

SELECT TOP (10) customer_id, revenue
FROM customer_sales
ORDER BY revenue DESC, customer_id;

SELECT customer_id, revenue
FROM customer_sales
ORDER BY revenue DESC, customer_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL Server's OFFSET and FETCH require ORDER BY and are available in SQL Server 2012 and later. In every dialect, a limit without a deterministic order returns an arbitrary eligible subset. Add a unique tie-breaker, such as customer_id, when repeatable results matter.

Get the current date and time

CURRENT_TIMESTAMP without parentheses is valid in all three and is the best interview default.

SELECT CURRENT_TIMESTAMP;

The value's type, precision, evaluation timing, and time zone are not identical. PostgreSQL's value is tied to the current transaction start; MySQL's current-time functions are constant within a statement; SQL Server's CURRENT_TIMESTAMP is equivalent to GETDATE(). For UTC or high precision, use dialect-specific functions and say what you intend. In a test, replace “now” with a fixed parameter so boundary cases are reproducible.

Truncate or bucket a timestamp

PostgreSQL: date_trunc is direct.

SELECT date_trunc('month', order_ts) AS order_month
FROM orders;

MySQL: there is no equivalent general date_trunc function. For a month-start date, format and cast explicitly.

SELECT CAST(DATE_FORMAT(order_ts, '%Y-%m-01') AS DATE) AS order_month
FROM orders;

SQL Server 2022 and later: use DATETRUNC. On earlier versions, use the established DATEADD and DATEDIFF expression.

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS order_month
FROM orders;

-- Earlier SQL Server versions
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS order_month
FROM orders;

These expressions can return different temporal types, and timestamp conversion can depend on the session time zone. In an interview, say whether “month” means UTC, a business time zone, or the stored local time. Around midnight, that choice changes the bucket.

Add or subtract dates

PostgreSQL: use interval arithmetic. MySQL: use DATE_ADD or DATE_SUB. SQL Server: use DATEADD and a negative number for subtraction.

-- PostgreSQL
SELECT order_ts + INTERVAL '7 days';
SELECT order_ts - INTERVAL '7 days';

-- MySQL
SELECT DATE_ADD(order_ts, INTERVAL 7 DAY);
SELECT DATE_SUB(order_ts, INTERVAL 7 DAY);

-- SQL Server
SELECT DATEADD(day, 7, order_ts);
SELECT DATEADD(day, -7, order_ts);

Do not mechanically translate “one month” into 30 days. Calendar-month arithmetic has different intent, and month-end inputs such as January 31 need explicit expected results. Daylight-saving transitions also make one calendar day different from 24 elapsed hours for time-zone-aware values.

Calculate a date difference

First clarify whether the question asks for calendar boundaries or elapsed duration. For calendar-day difference, common forms are:

-- PostgreSQL: date subtraction returns an integer number of days
SELECT CAST(end_ts AS DATE) - CAST(start_ts AS DATE) AS days;

-- MySQL: arguments are end, then start; time portions are ignored
SELECT DATEDIFF(end_ts, start_ts) AS days;

-- SQL Server: arguments are start, then end
SELECT DATEDIFF(day, CAST(start_ts AS DATE), CAST(end_ts AS DATE)) AS days;

For elapsed time, PostgreSQL timestamp subtraction returns an interval, and EXTRACT(EPOCH FROM (end_ts - start_ts)) returns seconds. MySQL offers TIMESTAMPDIFF(unit, start_ts, end_ts). SQL Server offers DATEDIFF and DATEDIFF_BIG.

The SQL Server caveat is important: DATEDIFF counts date-part boundaries crossed, not completed elapsed units. Two timestamps only a fraction of a second apart can return one day if they straddle midnight. MySQL's DATEDIFF ignores time portions entirely. State the intended semantics and test a same-day pair, a midnight-crossing pair, and reversed arguments.

Concatenate strings

CONCAT exists in modern versions of all three and is clearer than operators in cross-dialect work.

SELECT CONCAT(
  COALESCE(first_name, ''),
  ' ',
  COALESCE(last_name, '')
) AS full_name
FROM customers;

The explicit COALESCE calls are deliberate. MySQL's CONCAT returns null if any argument is null. PostgreSQL ignores null arguments, and SQL Server converts them to empty strings. Without normalization, identical-looking queries produce different results.

PostgreSQL commonly uses ||. SQL Server traditionally uses +. In MySQL, || normally means logical OR unless a special SQL mode changes it. Avoid operator translation when CONCAT expresses the intent.

Perform case-insensitive matching

PostgreSQL: ILIKE is a convenient extension.

SELECT customer_id
FROM customers
WHERE email ILIKE '%example.com';

MySQL and SQL Server: LIKE may be case-insensitive or case-sensitive depending on the expression's collation. Do not promise one behavior without knowing the schema and collation.

A recognizable portable fallback is LOWER(email) LIKE LOWER('%example.com'). It can prevent a normal index on email from being used, and Unicode case folding still follows database rules. For production, prefer the intended collation or an indexed normalized value. In an interview, state the fallback and its performance caveat.

Cast values

Standard CAST handles numeric conversion with the same basic form in every dialect:

SELECT CAST(amount AS DECIMAL(12, 2)) AS amount_rounded
FROM orders;

Text targets are already dialect-specific. PostgreSQL and SQL Server accept CAST(customer_id AS VARCHAR(100)); MySQL's documented target is CAST(customer_id AS CHAR(100)), which produces a nonbinary string. PostgreSQL also supports the shorthand amount::numeric. MySQL has targets such as SIGNED, DATE, and DATETIME. SQL Server supports CONVERT and TRY_CAST. Use an explicit length or precision and avoid relying on implicit conversion.

Failure behavior is not portable. SQL Server's TRY_CAST can return null for a failed supported conversion, while PostgreSQL normally raises an error for invalid input and MySQL behavior can depend on type and SQL mode. If dirty text is part of the prompt, discuss validation rather than inventing a universal safe cast.

Handle nulls

COALESCE(a, b, c), NULLIF(a, b), IS NULL, and IS NOT NULL are the portable choices. Use them unless the prompt demands a vendor feature.

SELECT COALESCE(discount_amount, 0) AS discount_amount
FROM orders
WHERE cancelled_at IS NULL;

MySQL's IFNULL and SQL Server's ISNULL are not interchangeable spellings. In MySQL, ISNULL(expr) is a null test, while SQL Server's ISNULL(expr, replacement) replaces null and applies SQL Server-specific type rules. COALESCE avoids that trap.

Remember that null is not equal to anything, including another null. Write column IS NULL, never column = NULL. Also decide whether replacing missing amounts with zero is valid business logic; syntactic portability cannot answer that question.

Filter booleans

PostgreSQL has a true boolean type, so WHERE is_active or WHERE is_active = TRUE is natural. MySQL treats TRUE and FALSE as 1 and 0, and BOOLEAN is effectively a small integer alias. SQL Server commonly stores flags as BIT and does not accept TRUE as the corresponding literal; use WHERE is_active = 1.

There is no single flag comparison that perfectly describes all three storage models. Ask for the column type or state your assumption. This is one place where being explicit is better than forcing superficial portability.

Use window functions and top N per group

The core window syntax is pleasantly consistent. This pattern returns three employees per department with deterministic tie-breaking:

WITH ranked_employees AS (
  SELECT department_id,
         employee_id,
         salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, employee_id
         ) AS rn
  FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked_employees
WHERE rn <= 3
ORDER BY department_id, rn;

This works in PostgreSQL, MySQL 8.0 and later, and supported SQL Server versions. MySQL 5.7 lacks window functions and common table expressions, so it needs a different strategy; mention that version boundary rather than silently assuming modern MySQL.

Use RANK instead of ROW_NUMBER if all employees tied at the cutoff should be included. That can return more than three rows. The outer query is necessary because these databases do not let this window result be filtered in the same query's WHERE clause. For portable null ordering, use ORDER BY CASE WHEN salary IS NULL THEN 1 ELSE 0 END, salary DESC instead of PostgreSQL's convenient NULLS LAST.

Worked translation: top revenue days in the last 30 days

Suppose the task is to return the ten highest-revenue UTC calendar days among paid orders in the trailing 30 days. Null amounts are explicitly treated as zero, ties are resolved by earlier date, and the range begins at the current timestamp minus 30 days.

PostgreSQL

SELECT CAST(date_trunc('day', ordered_at) AS DATE) AS order_day,
       COUNT(*) AS order_count,
       SUM(COALESCE(total_amount, 0)) AS revenue
FROM orders
WHERE ordered_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
  AND status = 'paid'
GROUP BY CAST(date_trunc('day', ordered_at) AS DATE)
ORDER BY revenue DESC, order_day
LIMIT 10;

MySQL

SELECT CAST(ordered_at AS DATE) AS order_day,
       COUNT(*) AS order_count,
       SUM(COALESCE(total_amount, 0)) AS revenue
FROM orders
WHERE ordered_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAY)
  AND status = 'paid'
GROUP BY CAST(ordered_at AS DATE)
ORDER BY revenue DESC, order_day
LIMIT 10;

SQL Server

SELECT TOP (10)
       CAST(ordered_at AS DATE) AS order_day,
       COUNT(*) AS order_count,
       SUM(COALESCE(total_amount, 0)) AS revenue
FROM orders
WHERE ordered_at >= DATEADD(day, -30, CURRENT_TIMESTAMP)
  AND status = 'paid'
GROUP BY CAST(ordered_at AS DATE)
ORDER BY revenue DESC, order_day;

The translations are syntactically close, but correctness still depends on assumptions. If ordered_at is not already expressed in UTC, casting it to a date may create the wrong day. A trailing 30 times 24 hours is also different from 30 complete calendar days. State which range the business wants and use a fixed as_of parameter in validation.

Test each version with orders just before, exactly at, and just after the lower boundary; timestamps on both sides of UTC midnight; null and negative amounts; multiple days tied on revenue; and no matching rows. Confirm output types as well as values. That is how you show the translation preserves meaning, not merely syntax.

How to handle dialect uncertainty during an interview

Keep moving. Name the concept first, write the syntax you know, and mark the translation point: “I need a month bucket here; in PostgreSQL that is date_trunc, while in SQL Server 2022 it is DATETRUNC.” Interviewers generally care more about correct relational logic than memorizing every function signature, but reversed date-difference arguments and invalid row-limit syntax are avoidable.

Prefer a small set of portable tools: CASE, COALESCE, CAST, explicit joins, conditional aggregation, and standard window specifications. Use vendor functions where they make the answer correct and readable, and label version-sensitive syntax. Never hide a semantic difference behind “the functions are basically the same.”

FAQ

Which SQL dialect should I use if none is specified?

Ask once. If the interviewer has no preference, state a dialect you know well and proceed consistently. PostgreSQL is a common choice, but consistency and explanation matter more than the label.

Is ANSI SQL enough for interviews?

It covers the relational core, but practical questions often need nonstandard row limiting, date arithmetic, or case-insensitive matching. Know the concepts and the handful of translations in this guide.

Are window functions portable across all three databases?

The common forms of ROW_NUMBER, RANK, LAG, LEAD, and aggregate windows are very similar in modern releases. Version matters most for older MySQL: window functions require MySQL 8.0 or later.

Why does the same date-difference query return different answers?

The function may count calendar boundaries, ignore time portions, or compute an elapsed interval. Argument order also differs between MySQL's DATEDIFF and SQL Server's DATEDIFF. Define the desired unit and boundary semantics before translating.

What is the most portable way to replace nulls?

Use COALESCE(expression, replacement). It is available in PostgreSQL, MySQL, and SQL Server. Still verify that replacement is correct business logic and that the resulting type is what you expect.

Should I optimize dialect-specific expressions during the interview?

Get a correct query first, then mention the main production concern. Examples include indexing normalized text for case-insensitive search, avoiding large offsets, using sargable timestamp ranges, and checking execution plans. Do not bury the solution under premature tuning.