Learn / SQL Server → MySQL / Queries & Pagination
Lesson 2 of 5

Queries & Pagination

The tables now exist in both worlds — the next friction point is shaping a result set. Limiting row counts, paging through results, and handling NULLs all use different keywords in MySQL than the ones muscle memory expects from T-SQL.

💡 The Bottom Line

TOP (n) becomes LIMIT n, and it moves from the front of the column list to the end of the query. OFFSET ... FETCH NEXT ... ROWS ONLY becomes the far shorter LIMIT n OFFSET m. ISNULL(a, b) becomes IFNULL(a, b) (or the standard COALESCE, which both engines support). Everything else about SELECT/WHERE/ORDER BY reads almost identically.

Limiting rows: TOP vs LIMIT

SQL Server (T-SQL)
SELECT TOP (10) *
FROM Orders
ORDER BY OrderDate DESC;
MySQL
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

📋 Key differences

  • TOP sits right after SELECT, before the column list; LIMIT sits at the very end of the query, after ORDER BY.
  • SQL Server's TOP without parentheses (SELECT TOP 10 *) is legal legacy syntax; MySQL has no equivalent bare form — it's always LIMIT n at the end.
  • TOP can take a PERCENT modifier (TOP 10 PERCENT); MySQL's LIMIT has no percent form — you'd compute the row count yourself if you needed that.

Paging: OFFSET-FETCH vs LIMIT-OFFSET

SQL Server (T-SQL)
SELECT *
FROM Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
MySQL
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 20;

Both statements return the same thing: skip the first 20 rows, return the next 10. SQL Server's ANSI-standard OFFSET ... FETCH NEXT ... ROWS ONLY is verbose but explicit about which number is the skip and which is the page size. MySQL's LIMIT count OFFSET offset is shorter, and MySQL also accepts a comma form — LIMIT offset, count — which reverses the argument order and is a common source of off-by-swap bugs when porting.

⚠️ Gotcha

MySQL's two-argument comma syntax, LIMIT 20, 10, means "skip 20, take 10" — offset first, count second — the opposite order from how the words read in LIMIT 10 OFFSET 20. If you mentally translate SQL Server's OFFSET 20 ROWS FETCH NEXT 10 ROWS into LIMIT 20, 10 by matching the numbers in the order they appeared, you'll get it backwards. Prefer the explicit LIMIT 10 OFFSET 20 form — it reads unambiguously and matches the count-then-offset order most people expect.

NULL handling: ISNULL vs IFNULL / COALESCE

SQL Server (T-SQL)
SELECT ISNULL(MiddleName, '') AS MiddleName
FROM Users;
MySQL
SELECT IFNULL(middle_name, '') AS middle_name
FROM users;

📋 Key differences

  • ISNULL(a, b) is SQL Server's non-standard, two-argument NULL substitution function; MySQL's direct equivalent is IFNULL(a, b).
  • Both engines support the ANSI-standard COALESCE(a, b, c, ...), which takes any number of arguments and returns the first non-NULL one — it's the more portable choice if you expect to support more than one database in the future.
  • Watch return-type behavior: SQL Server's ISNULL takes its result type from the first argument, which can cause silent truncation if the fallback value is longer than the first argument's declared type; COALESCE on both engines determines the type from all arguments, which is usually safer.

String functions: SUBSTRING differences

SQL Server (T-SQL)
SELECT SUBSTRING(Email, 1, 5) FROM Users;
MySQL
SELECT SUBSTRING(email, 1, 5) FROM users;
-- also: SUBSTR(email, 1, 5)

SUBSTRING(string, start, length) works the same way on both engines — 1-based indexing, three arguments. The differences show up at the edges: MySQL also accepts a negative start position (SUBSTRING(email, -5) returns the last 5 characters), which SQL Server's SUBSTRING does not support directly — you'd combine RIGHT() and LEN() to get the same effect in T-SQL.

🔧 Try It Yourself

Take a T-SQL paging query that uses OFFSET ... FETCH NEXT ... ROWS ONLY and rewrite it twice for MySQL: once using LIMIT count OFFSET offset, and once using the comma form LIMIT offset, count. Confirm both return the same rows — and notice how easy it would be to swap the numbers in the comma form by accident.

🙋 There Are No Dumb Questions

Q: Does MySQL support OFFSET ... FETCH at all, so I don't have to change my queries?
A: No — MySQL only understands its own LIMIT/LIMIT ... OFFSET syntax. The ANSI OFFSET n ROWS FETCH NEXT m ROWS ONLY form that SQL Server, PostgreSQL, and Oracle all accept is not valid MySQL syntax and will throw a syntax error — this is one of the few places MySQL didn't adopt the SQL standard's phrasing.

✅ Quick Check

In MySQL, what does LIMIT 20, 10 return?

Next up: once rows are flowing, the next question is how each engine assigns and retrieves identity values for new rows — IDENTITY and SCOPE_IDENTITY() have MySQL equivalents that behave a little differently.

← Back to
Next →