SQL LIMIT and Pagination

LIMIT returns only the first N rows of a result: the five newest orders, the three most expensive products, page 2 of a list. Combined with ORDER BY it answers “top N” questions, and with OFFSET it powers pagination in almost every web app.

LIMIT syntax

SELECT columnsFROM table_nameORDER BY columnLIMIT row_count [OFFSET skip];

LIMIT is the last step of a query: MySQL builds and sorts the whole result first, then keeps the requested rows.

Sample data

Top N rows

The three most expensive products:

SELECT product_name, priceFROM productsORDER BY price DESCLIMIT 3;

OFFSET: skip rows

OFFSET skips rows before LIMIT starts counting. Products 4 to 6 by price:

SELECT product_name, priceFROM productsORDER BY price DESCLIMIT 3 OFFSET 3;

MySQL also accepts the short form LIMIT offset, count, so LIMIT 3, 3 means the same. The order of the two numbers is easy to mix up; LIMIT 3 OFFSET 3 is clearer.

Pagination

For page p with n rows per page: LIMIT n OFFSET (p - 1) * n. Page 2 of the employee list, 5 per page:

SELECT emp_id, first_name, last_nameFROM employeesORDER BY emp_idLIMIT 5 OFFSET 5;

Nth highest value

A classic interview question: find the second highest salary. Sort the distinct salaries descending, skip one, take one:

SELECT DISTINCT salaryFROM employeesORDER BY salary DESCLIMIT 1 OFFSET 1;

To get the 3rd highest, use OFFSET 2. DENSE_RANK solves the same problem and also returns who earns it.

LIMIT with ties

Which employees are the two lowest paid in Engineering?

SELECT first_name, salaryFROM employeesWHERE dept_id = 1ORDER BY salaryLIMIT 2;

The answer is Amit (25,000) and one of the two people earning 72,000. LIMIT cuts strictly after 2 rows and does not care that Priya and Karan are tied. If ties matter, use RANK().

LIMIT in other databases

Performance: big offsets are slow

To answer LIMIT 20 OFFSET 100000, MySQL still finds and throws away 100,000 rows. For deep pages, use keyset pagination: remember the last value of the previous page and continue after it:

SELECT emp_id, first_nameFROM employeesWHERE emp_id > 104ORDER BY emp_idLIMIT 5;

With an index on emp_id, MySQL jumps straight to the right place, no matter how deep the page.

SELECT order_id, order_date, statusFROM ordersORDER BY order_date DESCLIMIT 3;

SELECT DISTINCT priceFROM productsORDER BY priceLIMIT 1 OFFSET 2;

LIMIT FAQ

SQL ORDER BY

Without ORDER BY, a database returns rows in whatever order is cheapest for it, and that order can change from one run to the next. ORDER BY sorts the result so it is predictable: highest salary first, newest order first, names A to Z.

ORDER BY syntax

SELECT columnsFROM table_nameORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

ASC (ascending: A to Z, small to large, old to new) is the default. DESC reverses it.

Sample data

Sort ascending

SELECT first_name, salaryFROM employeesORDER BY salary;

Sort descending

SELECT first_name, hire_dateFROM employeesORDER BY hire_date DESC;

Sort by several columns

Sort by department, and inside each department by salary from high to low:

SELECT dept_id, first_name, salaryFROM employeesORDER BY dept_id, salary DESC;

Ties are common. Priya and Karan both earn 72,000; add a second key so their order is defined:

SELECT first_name, salaryFROM employeesWHERE dept_id = 1ORDER BY salary DESC, first_name;

Sort by an expression or alias

ORDER BY runs after SELECT, so it can use a column alias:

SELECT first_name, salary, commission,       salary + IFNULL(commission, 0) AS total_payFROM employeesWHERE dept_id = 2ORDER BY total_pay DESC;

You can also sort by a column that is not in the SELECT list:

SELECT product_nameFROM productsORDER BY price DESCLIMIT 3;

Where do NULLs go?

MySQL sorts NULL before all values in ASC and after them in DESC. To put NULLs last in an ascending sort, sort by column IS NULL first:

SELECT first_name, commissionFROM employeesORDER BY commission IS NULL, commission;

A custom sort order with FIELD() and CASE

Show orders by status in a business order: Pending first, then Shipped, Delivered, Cancelled:

SELECT order_id, statusFROM ordersORDER BY FIELD(status, 'Pending', 'Shipped', 'Delivered', 'Cancelled'), order_id;

FIELD() is MySQL-specific. The portable way is a CASE expression:

SELECT order_id, statusFROM ordersORDER BY CASE status           WHEN 'Pending'   THEN 1           WHEN 'Shipped'   THEN 2           WHEN 'Delivered' THEN 3           ELSE 4         END,         order_id;

SELECT product_name, category, priceFROM productsORDER BY category, price;

SELECT customer_name, joined_onFROM customersORDER BY joined_on DESC;

ORDER BY FAQ

SQL WHERE Clause

Most questions you ask a database are about some rows, not all of them: the orders from January, the products that are out of stock, the employees in Pune. The WHERE clause filters rows so that only the ones matching a condition reach the result.

WHERE syntax

SELECT column1, column2FROM table_nameWHERE condition;

The condition is checked for every row. Rows where it is true are kept; rows where it is false or unknown (because of NULL) are dropped.

Sample data

Filter on a number

Which products cost less than 1,000?

SELECT product_name, priceFROM productsWHERE price < 1000;

Comparison operators

Products that are out of stock:

SELECT product_id, product_name, stockFROM productsWHERE stock = 0;

Orders that are not delivered yet:

SELECT order_id, customer_id, statusFROM ordersWHERE status <> 'Delivered';

Filter on text

Text values go in single quotes. With MySQL’s default collation the comparison ignores case:

SELECT first_name, last_name, cityFROM employeesWHERE city = 'Pune';

Filter on dates

Write dates as 'YYYY-MM-DD' text. MySQL compares them as dates, so > means “later than”:

SELECT first_name, hire_dateFROM employeesWHERE hire_date >= '2022-01-01'ORDER BY hire_date;

Filter on a calculated value

The condition can use expressions. Which order lines are worth more than 5,000?

SELECT oi.order_id, p.product_name, oi.quantity, p.price,       oi.quantity * p.price AS line_totalFROM order_items oiJOIN products p ON p.product_id = oi.product_idWHERE oi.quantity * p.price > 5000;

WHERE with UPDATE and DELETE

WHERE decides which rows an UPDATE or DELETE touches. Without it, every row changes:

UPDATE productsSET stock = 50WHERE product_id = 3; SELECT product_id, product_name, stock FROM products WHERE product_id = 3;

Common WHERE mistakes

• Comparing with NULL using =. WHERE commission = NULL never matches. Use IS NULL (NULL Values).
• Forgetting quotes around text. WHERE city = Pune makes MySQL look for a column called Pune.
• Quoting numbers. WHERE price < '1000' works, but MySQL has to convert the text first; keep numbers unquoted.
• Using a SELECT alias in WHERE. WHERE line_total > 5000 fails, because WHERE runs before SELECT.

SELECT first_name FROM employees WHERE city = Pune;

ERROR 1054 (42S22): Unknown column 'Pune' in 'where clause'

SELECT first_name, salaryFROM employeesWHERE salary <> 72000;

SELECT order_id, customer_id, order_dateFROM ordersWHERE order_date >= '2025-04-01';

SELECT product_name, priceFROM productsWHERE category = 'Furniture';

WHERE clause FAQ

SQL AND, OR and NOT Operators

A single condition is rarely enough. “Furniture and under 5,000″, “Pune or Delhi”, “not cancelled”: AND, OR and NOT combine conditions in a WHERE clause. The one rule that catches everyone is precedence: AND binds tighter than OR, so you need parentheses more often than you think.

Sample data

AND: every condition must be true

Furniture that costs less than 5,000:

SELECT product_name, category, priceFROM productsWHERE category = 'Furniture'  AND price < 5000;

OR: at least one condition must be true

Employees based in Pune or Delhi:

SELECT first_name, cityFROM employeesWHERE city = 'Pune'   OR city = 'Delhi';

NOT: reverse a condition

All orders except the delivered ones:

SELECT order_id, statusFROM ordersWHERE NOT status = 'Delivered';

NOT also combines with other operators: NOT IN, NOT BETWEEN, NOT LIKE, IS NOT NULL.

Precedence: AND before OR

Goal: Electronics or Stationery products that are in stock. This first attempt looks right but is wrong:

SELECT product_name, category, stockFROM productsWHERE category = 'Electronics'   OR category = 'Stationery'  AND stock > 0;

Parentheses say what you mean:

SELECT product_name, category, stockFROM productsWHERE (category = 'Electronics' OR category = 'Stationery')  AND stock > 0;

AND, OR and NULL

SQL uses three-valued logic: a condition can be TRUE, FALSE or UNKNOWN (when NULL is involved). WHERE keeps only TRUE rows.

SELECT first_name, commissionFROM employeesWHERE commission > 5000 OR commission < 5000;

A realistic filter

Engineering or Sales employees hired before 2022 who earn at least 90,000:

SELECT first_name, dept_id, hire_date, salaryFROM employeesWHERE (dept_id = 1 OR dept_id = 2)  AND hire_date < '2022-01-01'  AND salary >= 90000ORDER BY salary DESC;

SELECT product_name, price, stockFROM productsWHERE price < 500 OR stock > 100;

SELECT order_id, order_date, statusFROM ordersWHERE status = 'Delivered'  AND order_date >= '2025-01-01'  AND order_date <= '2025-03-31';

AND / OR FAQ

SQL SELECT DISTINCT

A plain SELECT returns one result row for every table row, so the same value often appears many times. SELECT DISTINCT removes the duplicates and returns each unique value, or each unique combination of values, only once.

Sample data

Duplicates without DISTINCT

Which cities do our employees work in?

SELECT cityFROM employeesORDER BY city;

The result has 12 rows because there are 12 employees: Bengaluru and Pune appear several times.

SELECT DISTINCT on one column

SELECT DISTINCT cityFROM employeesORDER BY city;

DISTINCT on several columns

With more than one column, DISTINCT removes rows where all the listed columns are the same. It works on the combination, not on each column separately:

SELECT DISTINCT dept_id, cityFROM employeesORDER BY dept_id, city;

DISTINCT and NULL

DISTINCT treats all NULLs as equal, so they collapse into one NULL row:

SELECT DISTINCT commissionFROM employeesORDER BY commission;

COUNT(DISTINCT column)

To count how many different values a column has, put DISTINCT inside COUNT:

SELECT COUNT(*)             AS employees,       COUNT(city)          AS rows_with_city,       COUNT(DISTINCT city) AS different_cities,       COUNT(DISTINCT dept_id) AS departments_usedFROM employees;

Which customers have placed at least one order? DISTINCT turns “one row per order” into “one row per customer”:

SELECT DISTINCT customer_idFROM ordersORDER BY customer_id;

DISTINCT vs GROUP BY

SELECT DISTINCT city FROM employees and SELECT city FROM employees GROUP BY city return the same rows. Use DISTINCT when you only want unique rows; use GROUP BY when you also want a count, sum or other aggregate per group:

SELECT city, COUNT(*) AS staffFROM employeesGROUP BY cityORDER BY staff DESC, city;

SELECT DISTINCT job_titleFROM employeesORDER BY job_title;

SELECT COUNT(DISTINCT category) AS categoriesFROM products;

SELECT DISTINCT FAQ

SQL Data Types

Every column has a data type that decides what values it accepts, how much space it takes and how it sorts and compares. Choosing well prevents bugs such as rounding errors in money or names cut short. This lesson shows the MySQL types you will actually use, and what MySQL 8.4 does when a value does not fit.

Numeric types

Add UNSIGNED to an integer type to allow only zero and positive numbers and double the upper limit.

DECIMAL vs FLOAT: why money must be exact

FLOAT and DOUBLE store numbers in binary, so most decimal fractions are only approximated. DECIMAL stores them exactly:

SELECT 0.1e0 + 0.2e0 AS double_sum,       0.1 + 0.2     AS decimal_sum;

Values that do not fit

MySQL 8.4 runs in strict mode by default: a value that does not fit is an error, not a silent change.

CREATE TABLE stock_check (qty TINYINT);INSERT INTO stock_check VALUES (300);

ERROR 1264 (22003): Out of range value for column 'qty' at row 1

String types

Text longer than the column allows is rejected:

CREATE TABLE users (username VARCHAR(8));INSERT INTO users VALUES ('sqatools_admin');

ERROR 1406 (22001): Data too long for column 'username' at row 1

ENUM accepts only the listed values:

CREATE TABLE tickets (id INT, status ENUM('open', 'in_progress', 'closed'));INSERT INTO tickets VALUES (1, 'open');INSERT INTO tickets VALUES (2, 'pending');

ERROR 1265 (01000): Data truncated for column 'status' at row 1

Date and time types

Date arithmetic understands the calendar:

SELECT DATE '2026-01-31' + INTERVAL 1 MONTH AS next_month,       DATE '2024-02-28' + INTERVAL 1 DAY   AS leap_day,       DATEDIFF('2026-12-25', '2026-01-01') AS days_between;

Learn more in Date & Time Functions.

BOOLEAN

MySQL has no separate boolean type: BOOLEAN is a synonym for TINYINT(1), TRUE is 1 and FALSE is 0.

CREATE TABLE features (name VARCHAR(20), enabled BOOLEAN);INSERT INTO features VALUES ('dark_mode', TRUE), ('beta_api', FALSE);SELECT name, enabled FROM features;

JSON

A JSON column stores validated JSON documents, and you can read values from them in queries:

CREATE TABLE settings (user_id INT, prefs JSON);INSERT INTO settings VALUES (1, '{"theme": "dark", "font_size": 14}');SELECT user_id,       prefs->>'$.theme'     AS theme,       prefs->>'$.font_size' AS font_sizeFROM settings;

How to choose a data type

• Ids and counts: INT (or BIGINT for very large tables).
• Money: DECIMAL(10,2), never FLOAT.
• Names, emails, codes: VARCHAR with a sensible limit.
• Long text: TEXT.
• Dates: DATE; date and time: DATETIME; “when was this row changed”: TIMESTAMP.
• Yes/no: BOOLEAN.
• Choose the smallest type that safely fits the data: smaller rows mean faster queries and smaller indexes.

Data types FAQ

The SQL Sample Database

Every lesson in this tutorial queries the same small company database, so you can focus on SQL instead of new data each time. It has six related tables: a company’s departments and employees, and its online shop’s customers, products, orders and order items. This page shows every row and gives you the script to create it on your own MySQL server.

How the tables are linked

• HR side: each employee belongs to a department and reports to a manager, who is also an employee (manager_id points back to employees). The chain goes four levels deep: CEO → manager → senior developer → developer.
• Shop side: a customer places orders; each order has one or more order items; each item is a product with a quantity. Line amount = quantity × price.

departments

Finance (dept 5) has no employees: useful for LEFT JOIN examples.

employees

Rows worth noticing:

• Meera Kapoor (100), the CEO, has no department and no manager (NULL).
• Priya Nair and Karan Shah both earn 72,000: a tie, which matters for RANK and DENSE_RANK.
• Only the three Sales staff have a commission; it is NULL for everyone else (NULL Values).

customers

David Fernandes has no email, and Manoj Pillai and Sara Thomas have never ordered.

products

The USB-C Hub is out of stock (stock = 0), and the Monitor Arm has never been ordered.

orders

order_items

A first look across the tables

How much did each order cost? This joins three tables. You will learn every part of it in the coming lessons:

SELECT o.order_id,       c.customer_name,       o.status,       SUM(oi.quantity * p.price) AS order_totalFROM orders oJOIN customers   c  ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products    p  ON p.product_id  = oi.product_idGROUP BY o.order_id, c.customer_name, o.statusORDER BY o.order_id;

Create it on your own server

Create the database, select it, then run the script below (copy it into the mysql client, MySQL Workbench or any SQL tool):

CREATE DATABASE company;USE company;

-- sqatools.in SQL tutorial: the "company" sample database (runs unchanged on MySQL 8.4 and SQLite)CREATE TABLE departments (  dept_id   INT PRIMARY KEY,  dept_name VARCHAR(50) NOT NULL,  location  VARCHAR(50));INSERT INTO departments VALUES(1, 'Engineering', 'Bengaluru'),(2, 'Sales', 'Mumbai'),(3, 'Marketing', 'Pune'),(4, 'HR', 'Delhi'),(5, 'Finance', 'Mumbai'); CREATE TABLE employees (  emp_id     INT PRIMARY KEY,  first_name VARCHAR(30) NOT NULL,  last_name  VARCHAR(30) NOT NULL,  dept_id    INT,  manager_id INT,  job_title  VARCHAR(40),  salary     INT,  commission INT,  hire_date  DATE,  city       VARCHAR(30),  FOREIGN KEY (dept_id) REFERENCES departments (dept_id));INSERT INTO employees VALUES(100, 'Meera', 'Kapoor', NULL, NULL, 'CEO', 250000, NULL, '2015-01-05', 'Mumbai'),(101, 'Asha', 'Rao', 1, 100, 'Engineering Manager', 125000, NULL, '2018-04-12', 'Bengaluru'),(102, 'Rahul', 'Mehta', 1, 101, 'Senior Developer', 98000, NULL, '2019-07-01', 'Bengaluru'),(103, 'Priya', 'Nair', 1, 101, 'QA Engineer', 72000, NULL, '2021-01-18', 'Pune'),(104, 'Karan', 'Shah', 1, 102, 'Developer', 72000, NULL, '2022-08-29', 'Bengaluru'),(105, 'Vikram', 'Singh', 2, 100, 'Sales Manager', 105000, 12000, '2017-11-20', 'Mumbai'),(106, 'Neha', 'Gupta', 2, 105, 'Sales Executive', 54000, 6000, '2022-03-14', 'Mumbai'),(107, 'Arjun', 'Das', 2, 105, 'Sales Executive', 48000, 4500, '2023-06-05', 'Kolkata'),(108, 'Kavya', 'Iyer', 3, 100, 'Marketing Lead', 88000, NULL, '2020-09-09', 'Pune'),(109, 'Rohan', 'Joshi', 3, 108, 'Content Writer', 45000, NULL, '2024-01-22', 'Pune'),(110, 'Sneha', 'Kulkarni', 4, 100, 'HR Manager', 78000, NULL, '2019-02-25', 'Delhi'),(111, 'Amit', 'Verma', 1, 101, 'Intern', 25000, NULL, '2025-06-02', 'Bengaluru'); CREATE TABLE customers (  customer_id   INT PRIMARY KEY,  customer_name VARCHAR(50) NOT NULL,  email         VARCHAR(60),  city          VARCHAR(30),  joined_on     DATE);INSERT INTO customers VALUES(1, 'Ananya Sharma', 'ananya@example.com', 'Mumbai', '2024-01-15'),(2, 'Rohit Kumar', 'rohit.k@example.com', 'Delhi', '2024-02-03'),(3, 'Fatima Khan', 'fatima@example.com', 'Hyderabad', '2024-03-21'),(4, 'David Fernandes', NULL, 'Goa', '2024-05-09'),(5, 'Ishita Bose', 'ishita@example.com', 'Kolkata', '2024-07-30'),(6, 'Manoj Pillai', 'manoj.p@example.com', 'Chennai', '2025-01-11'),(7, 'Sara Thomas', 'sara.t@example.com', 'Bengaluru', '2025-03-02'); CREATE TABLE products (  product_id   INT PRIMARY KEY,  product_name VARCHAR(50) NOT NULL,  category     VARCHAR(30),  price        INT,  stock        INT);INSERT INTO products VALUES(1, 'Wireless Mouse', 'Electronics', 799, 120),(2, 'Mechanical Keyboard', 'Electronics', 3499, 45),(3, 'USB-C Hub', 'Electronics', 1999, 0),(4, 'Office Chair', 'Furniture', 8999, 12),(5, 'Standing Desk', 'Furniture', 18999, 5),(6, 'Notebook Pack', 'Stationery', 249, 300),(7, 'Gel Pens', 'Stationery', 199, 250),(8, 'Desk Lamp', 'Furniture', 1499, 30),(9, 'Monitor Arm', 'Furniture', 2799, 18); CREATE TABLE orders (  order_id    INT PRIMARY KEY,  customer_id INT NOT NULL,  order_date  DATE NOT NULL,  status      VARCHAR(20) NOT NULL,  FOREIGN KEY (customer_id) REFERENCES customers (customer_id));INSERT INTO orders VALUES(1001, 1, '2025-01-05', 'Delivered'),(1002, 2, '2025-01-17', 'Delivered'),(1003, 1, '2025-02-02', 'Delivered'),(1004, 3, '2025-02-14', 'Cancelled'),(1005, 4, '2025-03-08', 'Delivered'),(1006, 5, '2025-03-22', 'Shipped'),(1007, 2, '2025-04-10', 'Delivered'),(1008, 1, '2025-04-28', 'Pending'),(1009, 3, '2025-05-15', 'Delivered'),(1010, 5, '2025-06-01', 'Delivered'); CREATE TABLE order_items (  order_id   INT,  product_id INT,  quantity   INT NOT NULL,  PRIMARY KEY (order_id, product_id),  FOREIGN KEY (order_id) REFERENCES orders (order_id),  FOREIGN KEY (product_id) REFERENCES products (product_id));INSERT INTO order_items VALUES(1001, 2, 1), (1001, 6, 5),(1002, 4, 1),(1003, 2, 1), (1003, 1, 1),(1004, 5, 1),(1005, 6, 10), (1005, 7, 4),(1006, 3, 2), (1006, 8, 1),(1007, 1, 1),(1008, 4, 2), (1008, 8, 2),(1009, 2, 1),(1010, 6, 3), (1010, 7, 2);