How do I work with NULL values in SQL?

By Wayan in Learning SQL 0 Comment

Real data is rarely complete. A customer may not have provided a phone number, an order may not yet have a shipping date, and a book may not yet have been assigned a category. SQL uses a special marker, NULL, to represent this missing or unknown information. Working with NULL correctly is one of the most important skills in SQL, because NULL does not behave like ordinary values in comparisons, arithmetic, or aggregation.

In this tutorial, you will learn what NULL really means, how to test for it, how it interacts with operators and functions, and how to replace it with sensible defaults. You will also see the most common mistakes beginners make with NULL and how to avoid them.

Prerequisites

To follow along, you should be comfortable with:

  • writing a basic SELECT statement;
  • filtering rows with WHERE;
  • running SQL against a local database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).

If you have not created a sample database yet, the setup script below will produce everything you need.

Sample Database

We will use a small online bookstore schema with two tables: authors and books.

CREATE TABLE authors (
    author_id   INTEGER      PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books (
    book_id       INTEGER       PRIMARY KEY,
    title         VARCHAR(200)  NOT NULL,
    author_id     INTEGER       NOT NULL,
    category      VARCHAR(50),
    price         DECIMAL(8, 2),
    discount      DECIMAL(4, 2),
    published_on  DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country) VALUES
    (1, 'Jane Austen',              'United Kingdom'),
    (2, 'Haruki Murakami',          'Japan'),
    (3, 'Chimamanda Ngozi Adichie',  NULL),
    (4, 'Gabriel Garcia Marquez',   'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, discount, published_on) VALUES
    (1, 'Pride and Prejudice',           1, 'Classic',    12.50, 1.00, '1813-01-28'),
    (2, 'Emma',                          1, 'Classic',    10.00, NULL, '1815-12-23'),
    (3, 'Norwegian Wood',                2, 'Fiction',    15.75, 2.00, '1987-09-04'),
    (4, 'Kafka on the Shore',            2, 'Fiction',    18.20, NULL, '2002-09-12'),
    (5, 'Half of a Yellow Sun',          3, 'Historical', 16.00, 1.50, '2006-08-11'),
    (6, 'Americanah',                    3, 'Contemporary', NULL, NULL, '2013-05-14'),
    (7, 'One Hundred Years of Solitude', 4, 'Classic',    22.00, 3.00, '1967-05-30'),
    (8, 'Untitled Draft',                2,  NULL,         NULL, NULL,  NULL);

Notice that several columns intentionally contain NULL:

  • authors.country is NULL for one author (unknown country).
  • books.category, books.price, books.discount, and books.published_on are NULL for one or more books.

We will use these rows to demonstrate how NULL behaves.

What Does NULL Actually Mean?

NULL is a marker that represents missing, unknown, or unavailable information. It is not:

  • the number zero;
  • an empty string '';
  • the boolean value false;
  • a “default” value.

Two NULL values are not considered equal to each other in comparisons, because “unknown = unknown” is itself unknown.

Three-Valued Logic

Most languages use two-valued boolean logic: a condition is either true or false. SQL uses three-valued logic: true, false, and unknown. Any comparison involving NULL returns unknown.

Expression Result
1 = 1 true
1 = 2 false
1 = NULL unknown
NULL = NULL unknown
NULL <> 'x' unknown

The WHERE clause keeps a row only when the condition evaluates to true. Rows for which the condition is false or unknown are excluded.

Basic Syntax

To test whether a column contains NULL, use IS NULL or IS NOT NULL:

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

SELECT column_list
FROM table_name
WHERE column_name IS NOT NULL;

Do not use = or <> with NULL. They will not raise an error, but they will never match anything.

Practical Example

Suppose the bookstore manager wants to see every book that is missing a category, so the catalog team can fill in the gaps.

SELECT
    book_id,
    title,
    category
FROM books
WHERE category IS NULL;

Read the query in logical order:

  1. FROM books — start with every row in the books table.
  2. WHERE category IS NULL — keep only rows whose category is missing.
  3. SELECT book_id, title, category — return these three columns.

Expected Result

book_id title category
8 Untitled Draft NULL

Only one row is returned because only Untitled Draft has a missing category.

Additional Examples

Finding Rows Where a Column Is Not NULL

To list every book that already has a category:

SELECT
    book_id,
    title,
    category
FROM books
WHERE category IS NOT NULL
ORDER BY book_id;

NULL and Inequality

A common surprise is that <> does not return rows where the column is NULL:

SELECT
    book_id,
    title,
    category
FROM books
WHERE category <> 'Classic';

This query returns fiction, historical, and contemporary books, but not Untitled Draft, because NULL <> 'Classic' is unknown. To include rows with a missing category, add an explicit NULL check:

SELECT
    book_id,
    title,
    category
FROM books
WHERE category <> 'Classic'
   OR category IS NULL;

NULL in Arithmetic

Any arithmetic expression involving NULL produces NULL:

SELECT
    book_id,
    title,
    price,
    discount,
    price - discount AS final_price
FROM books;

For any row where either price or discount is NULL, final_price is also NULL. Zero and NULL are not interchangeable.

Replacing NULL with COALESCE

COALESCE returns the first non-NULL argument. It is the portable, ANSI-standard way to substitute a default value for a missing one.

SELECT
    book_id,
    title,
    price,
    discount,
    price - COALESCE(discount, 0) AS final_price
FROM books;

Now the discount is treated as 0 when it is missing, and final_price is computed correctly for every row that has a price.

You can pass more than two arguments; COALESCE returns the first one that is not NULL:

SELECT
    author_id,
    author_name,
    COALESCE(country, 'Unknown') AS country
FROM authors;

NULLIF: The Inverse of COALESCE

NULLIF(a, b) returns NULL when a equals b, and returns a otherwise. It is useful for avoiding division by zero:

SELECT
    book_id,
    title,
    price / NULLIF(discount, 0) AS price_to_discount_ratio
FROM books;

If discount is 0, NULLIF converts it to NULL, and the division produces NULL instead of raising an error.

NULL and Aggregate Functions

Most aggregate functions ignore NULL values:

SELECT
    COUNT(*)          AS total_rows,
    COUNT(price)      AS rows_with_price,
    AVG(price)        AS average_price,
    SUM(discount)     AS total_discount
FROM books;
  • COUNT(*) counts every row, including rows where all columns are NULL.
  • COUNT(price) counts only rows where price IS NOT NULL.
  • AVG(price) is the average of the non-NULL prices; it is not affected by rows where price is NULL.
  • SUM(discount) adds only non-NULL discounts. If every value is NULL, SUM returns NULL, not 0.

This distinction matters when you report metrics: AVG over a partially-null column is the average of the values that exist, not the average of “value or zero”.

NULL and ORDER BY

Databases differ in where NULL values appear when you sort:

  • PostgreSQL and Oracle Database place NULL last in ascending order by default.
  • MySQL, MariaDB, SQL Server, and SQLite place NULL first in ascending order by default.

To control the placement portably where supported, use NULLS FIRST or NULLS LAST (PostgreSQL, Oracle Database, and SQLite 3.30+):

SELECT
    book_id,
    title,
    published_on
FROM books
ORDER BY published_on ASC NULLS LAST;

MySQL, MariaDB, and SQL Server do not support NULLS LAST directly; you can emulate it with an expression:

SELECT
    book_id,
    title,
    published_on
FROM books
ORDER BY
    CASE WHEN published_on IS NULL THEN 1 ELSE 0 END,
    published_on ASC;

NULL and DISTINCT / GROUP BY

DISTINCT and GROUP BY treat all NULL values as belonging to a single group, even though NULL = NULL is not true in a comparison:

SELECT DISTINCT category
FROM books;

This query returns each category once, plus a single row for NULL.

NULL and IN

Be careful when a subquery used with IN can return NULL:

SELECT title
FROM books
WHERE author_id NOT IN (SELECT author_id FROM authors WHERE country IS NULL);

If the subquery returns any NULL, the entire NOT IN condition becomes unknown for every row, and no rows are returned. Prefer NOT EXISTS when the subquery may produce NULL:

SELECT b.title
FROM books AS b
WHERE NOT EXISTS (
    SELECT 1
    FROM authors AS a
    WHERE a.author_id = b.author_id
      AND a.country IS NULL
);

Using NULL from Application Code

When you fetch nullable columns in JDBC, primitive types cannot represent NULL. Use the wrapper getters, or explicitly test the result with ResultSet.wasNull() after reading the value:

String sql = """
        SELECT book_id, title, price, discount
        FROM books
        WHERE book_id = ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, bookId);

    try (ResultSet resultSet = statement.executeQuery()) {
        if (resultSet.next()) {
            BigDecimal price = resultSet.getBigDecimal("price");
            BigDecimal discount = resultSet.getBigDecimal("discount");

            // getBigDecimal returns null for SQL NULL,
            // so an explicit wasNull() check is optional here.
            if (discount == null) {
                discount = BigDecimal.ZERO;
            }

            BigDecimal finalPrice = price == null
                    ? null
                    : price.subtract(discount);
        }
    }
}

When inserting a NULL, use PreparedStatement.setNull(index, sqlType) rather than passing an empty string or zero.

Common Mistakes

Comparing a Value to NULL with =

Incorrect:

SELECT *
FROM books
WHERE category = NULL;

Correct:

SELECT *
FROM books
WHERE category IS NULL;

The first query returns zero rows because category = NULL evaluates to unknown, never true.

Assuming NULL Equals NULL

The following query does not return every row:

SELECT *
FROM books
WHERE category = category;

For rows where category IS NULL, the expression NULL = NULL is unknown, so those rows are excluded. If you truly want every row, remove the WHERE clause.

Treating NULL as Zero in Arithmetic

Incorrect assumption:

SELECT
    book_id,
    price - discount AS final_price
FROM books;

For rows where discount IS NULL, final_price will also be NULL — not price. Use COALESCE:

SELECT
    book_id,
    price - COALESCE(discount, 0) AS final_price
FROM books;

Using NOT IN with a Nullable Subquery

If a subquery inside NOT IN can return NULL, the outer query silently returns no rows. Prefer NOT EXISTS when nullability is possible.

Expecting Aggregates to Fail on NULL

Aggregates skip NULL instead of raising an error. If AVG(price) looks “too high” or SUM(discount) looks “too low”, check whether the source column contains NULL values and whether that is the behavior you want.

Database Compatibility

NULL, IS NULL, IS NOT NULL, COALESCE, and NULLIF are part of standard SQL and work in:

  • PostgreSQL;
  • MySQL;
  • MariaDB;
  • SQL Server;
  • Oracle Database;
  • SQLite.

Notable differences:

  • NULLS FIRST / NULLS LAST are supported by PostgreSQL, Oracle Database, and SQLite 3.30 and later. MySQL, MariaDB, and SQL Server require a CASE expression in ORDER BY to emulate the behavior.
  • Default NULL ordering differs between databases, as noted earlier.
  • ISNULL exists in both SQL Server and MySQL, but with different meanings. In SQL Server, ISNULL(a, b) behaves like COALESCE(a, b). In MySQL, ISNULL(x) is a single-argument function that returns 1 when x IS NULL. Prefer the portable COALESCE and IS NULL forms.
  • NVL is Oracle Database’s non-standard equivalent of COALESCE with two arguments. Prefer COALESCE for portability.
  • Empty string vs. NULL. Oracle Database historically treats an empty string '' as NULL for VARCHAR2 columns. Other databases treat '' and NULL as distinct values.

Consult the documentation for your database and version when in doubt.

Best Practices

  • Model missing data deliberately. Decide up front whether a column should allow NULL. If a value is always required, declare the column NOT NULL.
  • Use IS NULL / IS NOT NULL for tests. Never write = NULL or <> NULL.
  • Use COALESCE for portable defaults. It is standard SQL and easier to read than vendor-specific functions such as NVL or ISNULL.
  • Guard against NULL in arithmetic. Wrap potentially null operands with COALESCE(column, 0) when zero is the correct substitute.
  • Beware of NOT IN with nullable subqueries. Prefer NOT EXISTS.
  • Document nullability at the application boundary. In Java, use wrapper types or Optional for columns that can be NULL, and use PreparedStatement.setNull when inserting.
  • Check aggregate semantics. Know that COUNT(*), COUNT(column), and SUM(column) treat NULL differently.
  • Be explicit about ordering. If sort order matters, specify NULLS FIRST or NULLS LAST where supported, or emulate it with CASE.

Conclusion

You learned that NULL represents missing or unknown information in SQL, that comparisons involving NULL use three-valued logic, and that you must use IS NULL and IS NOT NULL instead of = and <> to test for it. You also saw how COALESCE and NULLIF help you substitute defaults and guard against division by zero, and how NULL affects arithmetic, aggregation, sorting, and NOT IN. Treating NULL carefully will save you from a large class of subtle, silent bugs.

How do I delete data safely from a SQL table?

By Wayan in Learning SQL 0 Comment

Removing data from a database sounds simple, but a single missing clause can wipe out an entire table. In this tutorial, you will learn how to use the SQL DELETE statement safely: how to target the exact rows you want to remove, how to verify your target before deleting, and how to use transactions to recover from mistakes.

By the end, you will be able to confidently delete rows without risking accidental data loss.

Prerequisites

To follow along, you should:

  • Have a working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, or SQLite).
  • Know how to run SQL statements in a client such as psql, MySQL Workbench, DBeaver, or the command line.
  • Be familiar with SELECT and WHERE from earlier tutorials.

If you are new to SQL, start with How do I retrieve data using a SELECT statement? and How do I filter SQL query results using WHERE?

Sample Database

We will use an online bookstore as our sample domain. Create a customers table and insert a few rows so you can practice deletions safely.

CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(150) NOT NULL,
    status        VARCHAR(20)  NOT NULL,
    created_at    DATE         NOT NULL
);

INSERT INTO customers (customer_id, customer_name, email, status, created_at) VALUES
    (1, 'Alice Johnson',   '[email protected]',   'active',   '2024-01-15'),
    (2, 'Bob Smith',       '[email protected]',     'inactive', '2023-06-20'),
    (3, 'Carol Davis',     '[email protected]',   'active',   '2024-03-10'),
    (4, 'David Miller',    '[email protected]',   'inactive', '2022-11-05'),
    (5, 'Eva Thompson',    '[email protected]',     'active',   '2025-02-28');

This gives us five customers with a mix of active and inactive statuses.

Warning: Run every example in this tutorial in a disposable learning database, never in production.

Basic Syntax

The general form of a DELETE statement is:

DELETE FROM table_name
WHERE condition;

Key points:

  • DELETE FROM table_name names the table you want to remove rows from.
  • WHERE condition decides which rows are removed.
  • Without a WHERE clause, every row in the table is deleted.

DELETE removes rows but keeps the table structure (columns, indexes, constraints) intact.

Step 1: Verify the Target Rows First

Before running any DELETE, always run a SELECT with the same WHERE clause. This is the single most important habit to prevent data loss.

Suppose we want to remove the customer with customer_id = 4. First, check exactly which rows match:

SELECT customer_id, customer_name, email, status
FROM customers
WHERE customer_id = 4;

Expected Result

customer_id customer_name email status
4 David Miller [email protected] inactive

The result shows exactly one row, which is what we expect. Now the DELETE is safe to run.

Step 2: Perform the Delete

Reuse the same WHERE clause you just verified:

DELETE FROM customers
WHERE customer_id = 4;

The database will report the number of rows affected (for example, 1 row deleted). Confirm the row is gone:

SELECT customer_id, customer_name
FROM customers
ORDER BY customer_id;

Expected Result

customer_id customer_name
1 Alice Johnson
2 Bob Smith
3 Carol Davis
5 Eva Thompson

Additional Examples

Deleting Multiple Rows with a Condition

Remove all customers whose status is inactive:

SELECT customer_id, customer_name, status
FROM customers
WHERE status = 'inactive';

If the result matches the rows you intend to delete, run:

DELETE FROM customers
WHERE status = 'inactive';

Deleting Rows That Match a Date Condition

Remove customers created before 2024:

DELETE FROM customers
WHERE created_at < DATE '2024-01-01';

Handling NULL in Delete Conditions

If a column allows NULL, remember that NULL is not a value you can compare with =. To delete rows where email is missing:

DELETE FROM customers
WHERE email IS NULL;

Never write WHERE email = NULL. That condition is never true, so no rows are deleted, and you might incorrectly assume the table has no such rows.

Using a Transaction to Stay Safe

A transaction lets you delete rows and then decide whether to keep the change (COMMIT) or undo it (ROLLBACK).

BEGIN;

DELETE FROM customers
WHERE status = 'inactive';

-- Inspect the effect before committing
SELECT customer_id, customer_name, status
FROM customers;

-- If something looks wrong:
ROLLBACK;

-- If everything looks correct:
-- COMMIT;

Explanation:

  1. BEGIN starts a transaction.
  2. The DELETE removes matching rows, but the change is not yet permanent.
  3. You verify the result with a SELECT.
  4. ROLLBACK reverts the deletion. COMMIT makes it permanent.

Transaction behavior varies between database systems and storage engines. In MySQL, for example, the underlying engine must be transactional (InnoDB) for ROLLBACK to work.

Using Parameters from Application Code

When you delete from Java, always use parameterized queries instead of string concatenation:

String sql = """
        DELETE FROM customers
        WHERE customer_id = ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, customerId);
    int rowsDeleted = statement.executeUpdate();
    System.out.println(rowsDeleted + " row(s) deleted.");
}

Parameters protect against SQL injection and correctly handle data types.

Common Mistakes

Forgetting the WHERE Clause

Incorrect:

DELETE FROM customers;

This removes every row in the customers table. Unless you truly intend that, always include a WHERE clause.

Comparing to NULL with =

Incorrect:

DELETE FROM customers
WHERE email = NULL;

Correct:

DELETE FROM customers
WHERE email IS NULL;

NULL represents missing information, so equality comparisons with NULL always return unknown, not true.

Confusing DELETE with TRUNCATE and DROP

  • DELETE removes rows and can be rolled back inside a transaction.
  • TRUNCATE removes all rows quickly but is usually non-transactional and may reset auto-increment counters.
  • DROP TABLE removes the entire table, including its structure.

Do not use TRUNCATE or DROP TABLE when you only want to remove selected rows.

Ignoring Foreign Key Constraints

If another table references the row you are deleting (for example, an orders table with a customer_id foreign key), the database may:

  • reject the delete;
  • cascade the delete to related rows (ON DELETE CASCADE);
  • set related columns to NULL (ON DELETE SET NULL).

Understand the constraints before deleting parent rows.

Database Compatibility

The basic DELETE ... WHERE ... syntax is standard SQL and works in:

  • PostgreSQL
  • MySQL
  • MariaDB
  • SQL Server
  • Oracle Database
  • SQLite

Differences to be aware of:

  • DELETE with JOIN: MySQL, MariaDB, and SQL Server support multi-table delete syntax. PostgreSQL uses DELETE ... USING. Oracle and SQLite require subqueries.
  • RETURNING clause: PostgreSQL and Oracle (RETURNING INTO) support returning deleted rows. MySQL and SQLite do not.
  • Auto-commit: Some clients auto-commit each statement. Explicitly use BEGIN / COMMIT when you need transactional safety.

When in doubt, consult the documentation for your database version.

Best Practices

  • Always run SELECT first with the same WHERE clause you plan to use in DELETE.
  • Never omit WHERE unless you deliberately want to empty the table.
  • Wrap risky deletes in a transaction so you can ROLLBACK on mistakes.
  • Back up important data before large or irreversible deletions.
  • Use parameterized queries in application code to avoid SQL injection.
  • Understand foreign keys and cascading rules before deleting parent rows.
  • Prefer a soft delete (for example, status = 'inactive' or a deleted_at timestamp) when you may need to recover the data later.
  • Use least-privilege accounts: application users should only have DELETE rights on tables where it is necessary.

Conclusion

You learned how to delete data safely from a SQL table by verifying the target rows with SELECT, applying DELETE with a precise WHERE clause, and using transactions to guard against mistakes. The most important rule is simple: never run a DELETE you have not first previewed with SELECT.

How do I update existing data in a SQL table?

By Wayan in Learning SQL 0 Comment

Data stored in a database rarely stays the same forever. Prices change, customers move, orders are shipped, and inventory goes up and down. When you need to modify rows that already exist in a table, SQL provides the UPDATE statement.

In this tutorial you will learn how to change the value of one or more columns in one or more rows using UPDATE, how to combine it safely with WHERE, how to use expressions and values from other tables, and — most importantly — how to avoid the single most common accident in SQL: updating every row in a table by mistake.

Prerequisites

To follow along, you need:

  • A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
  • A client where you can execute SQL statements, such as DBeaver, psql, mysql, or sqlite3.
  • Basic familiarity with SELECT and the WHERE clause. If you have not read the previous tutorial on filtering rows with WHERE, do that first — UPDATE relies on the same filtering rules.

If you have not created a sample database yet, the setup script in the next section will produce everything you need.

Sample Database

We continue with the small online bookstore schema introduced in earlier tutorials. It has two tables: authors and books.

CREATE TABLE authors (
    author_id   INTEGER      PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books (
    book_id      INTEGER       PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country) VALUES
    (1, 'Jane Austen',              'United Kingdom'),
    (2, 'Haruki Murakami',          'Japan'),
    (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
    (4, 'Gabriel Garcia Marquez',   'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock, published_on) VALUES
    (1, 'Pride and Prejudice',           1, 'Classic',      12.50, 20, '1813-01-28'),
    (2, 'Emma',                          1, 'Classic',      10.00,  0, '1815-12-23'),
    (3, 'Norwegian Wood',                2, 'Fiction',      15.75, 12, '1987-09-04'),
    (4, 'Kafka on the Shore',            2, 'Fiction',      18.20,  5, '2002-09-12'),
    (5, 'Half of a Yellow Sun',          3, 'Historical',   16.00,  8, '2006-08-11'),
    (6, 'Americanah',                    3, 'Contemporary', 14.50,  3, '2013-05-14'),
    (7, 'One Hundred Years of Solitude', 4, 'Classic',      22.00, 25, '1967-05-30'),
    (8, 'Love in the Time of Cholera',   4, 'Classic',      19.99,  0, '1985-09-05'),
    (9, 'Unknown Title',                 2, NULL,           13.00,  4, NULL);

Basic Syntax

An UPDATE statement changes the values of specified columns in the rows that match a condition.

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Each clause has a specific role:

  • UPDATE table_name — the table whose rows will be modified.
  • SET column = value — the columns to change, and the new values or expressions. You can update several columns in a single statement by separating them with commas.
  • WHERE condition — restricts which rows are updated. If you omit WHERE, every row in the table is updated.

Warning: An UPDATE without a WHERE clause modifies every row in the table. Practice these statements only in a disposable learning database or after taking a verified backup.

Verify Before You Modify

Before running an UPDATE, always run a SELECT with the same WHERE clause first. This lets you see exactly which rows will be affected.

SELECT
    book_id,
    title,
    price
FROM books
WHERE book_id = 2;

Only after you have confirmed the target rows should you run the actual UPDATE.

Practical Example

Suppose the bookstore wants to correct the price of Emma (book id 2) to 11.00.

Step 1 — inspect the row:

SELECT
    book_id,
    title,
    price
FROM books
WHERE book_id = 2;
book_id title price
2 Emma 10.00

Step 2 — update the row:

UPDATE books
SET price = 11.00
WHERE book_id = 2;

Step 3 — verify the change:

SELECT
    book_id,
    title,
    price
FROM books
WHERE book_id = 2;

Expected Result

book_id title price
2 Emma 11.00

Most database clients also report the number of rows affected (for example, UPDATE 1), which is a useful sanity check.

Additional Examples

1. Updating Multiple Columns at Once

You can change several columns in one statement by listing them in the SET clause.

UPDATE books
SET price    = 20.00,
    category = 'Literary Classic'
WHERE book_id = 7;

This is more efficient than running two separate updates and guarantees that both changes happen together.

2. Using Expressions in SET

The new value does not have to be a constant. It can be any expression, including one that references the current value of the column.

Apply a 10% discount to every book in the 'Classic' category:

UPDATE books
SET price = price * 0.90
WHERE category = 'Classic';

Increase the stock of Kafka on the Shore by 3 copies:

UPDATE books
SET stock = stock + 3
WHERE book_id = 4;

The right-hand side of = sees the value before the update, so stock = stock + 3 means “new stock equals old stock plus three”.

3. Updating Many Rows That Share a Condition

UPDATE is not limited to a single row. Any WHERE condition that is valid in a SELECT is also valid here.

Mark every out-of-stock book as 'Fiction' no — better example — bump the price of every book published before the year 2000:

UPDATE books
SET price = price + 1.00
WHERE published_on < DATE '2000-01-01';

Before running it, check which rows will change:

SELECT
    book_id,
    title,
    published_on,
    price
FROM books
WHERE published_on < DATE '2000-01-01';

4. Setting a Column to NULL

To clear a value, assign NULL in the SET clause. Note the difference between the assignment = NULL (valid) and the comparison = NULL (invalid — you must use IS NULL for comparisons).

UPDATE books
SET category = NULL
WHERE book_id = 9;

The column must allow NULL (that is, it must not have a NOT NULL constraint) or the statement will fail.

5. Using a Value from Another Table

Sometimes the new value depends on data in a different table. A portable way to express this is a subquery in SET.

Suppose the authors table gains a discount_rate column, and you want every book to inherit its author’s discount. In ANSI-standard SQL you can write:

UPDATE books
SET price = price * (
    SELECT 1 - a.discount_rate
    FROM authors AS a
    WHERE a.author_id = books.author_id
)
WHERE EXISTS (
    SELECT 1
    FROM authors AS a
    WHERE a.author_id = books.author_id
);

PostgreSQL and SQL Server also support the more readable UPDATE ... FROM extension, and MySQL/MariaDB support a joined UPDATE. These are labeled in the Database Compatibility section below.

6. Limiting the Number of Rows Updated

Some databases allow you to cap the number of updated rows, which is useful for controlled batch updates.

MySQL, MariaDB, SQLite

UPDATE books
SET stock = stock + 1
WHERE category = 'Classic'
ORDER BY book_id
LIMIT 3;

PostgreSQL, SQL Server, and Oracle Database do not support LIMIT directly in UPDATE. On those systems you typically restrict the target rows through a subquery on the primary key.

7. Updating from Application Code

When the new value comes from user input, always use a parameterized query. This prevents SQL injection and lets the database reuse the prepared plan.

String sql = """
        UPDATE books
        SET price = ?
        WHERE book_id = ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setBigDecimal(1, newPrice);
    statement.setLong(2, bookId);

    int updatedRows = statement.executeUpdate();
    if (updatedRows == 0) {
        // No row matched the WHERE clause — decide how to handle it.
    }
}

executeUpdate() returns the number of affected rows, which you should check when you expect exactly one row to change.

8. Wrapping Related Updates in a Transaction

When two or more updates must succeed together — or fail together — run them inside a transaction.

BEGIN;

UPDATE books
SET stock = stock - 1
WHERE book_id = 3;

UPDATE books
SET stock = stock + 1
WHERE book_id = 4;

COMMIT;

If something goes wrong between the two statements, ROLLBACK reverts both changes so the total stock across the two books stays consistent. Transaction behavior varies between database systems and storage engines; consult your database’s documentation for details.

Common Mistakes

Forgetting the WHERE Clause

Incorrect:

UPDATE books
SET price = 9.99;

This is syntactically valid, but it sets the price of every book in the table to 9.99. Always double-check that a WHERE clause is present before executing an UPDATE.

Comparing to NULL with =

Incorrect:

UPDATE books
SET category = 'Uncategorized'
WHERE category = NULL;

The expression category = NULL evaluates to unknown, never true, so this statement updates zero rows.

Correct:

UPDATE books
SET category = 'Uncategorized'
WHERE category IS NULL;

Assuming an UPDATE Always Affects a Row

If the WHERE condition matches nothing, UPDATE succeeds and reports zero rows affected. It does not raise an error. In application code, check the returned row count when you expect exactly one row to change.

Mixing AND and OR Without Parentheses

Incorrect:

UPDATE books
SET price = price * 0.90
WHERE category = 'Classic' OR category = 'Fiction'
  AND stock > 0;

Because AND binds tighter than OR, this discounts all classic books plus in-stock fiction books, which is probably not what you want.

Correct:

UPDATE books
SET price = price * 0.90
WHERE (category = 'Classic' OR category = 'Fiction')
  AND stock > 0;

Reading a Column Twice in One SET

Incorrect assumption:

UPDATE books
SET price = price * 1.10,
    stock = price;         -- Which "price" is this — old or new?

In standard SQL, all right-hand-side expressions in a single UPDATE see the row’s values as they were before the statement started. So stock = price here uses the old price, not the newly increased one. Behavior in edge cases can differ between databases, so avoid relying on evaluation order — use two statements (inside a transaction) or a temporary variable if you need the intermediate value.

Database Compatibility

The basic form of UPDATE shown in the Basic Syntax section is part of ANSI SQL and works in:

  • PostgreSQL
  • MySQL
  • MariaDB
  • SQL Server
  • Oracle Database
  • SQLite

Notable differences you may encounter:

  • UPDATE ... FROM. PostgreSQL and SQL Server support an extension that joins other tables directly:
PostgreSQL, SQL Server

UPDATE books
  SET price = price * (1 - a.discount_rate)
  FROM authors AS a
  WHERE a.author_id = books.author_id;
  • Multi-table UPDATE. MySQL and MariaDB use a JOIN directly in the UPDATE:
MySQL, MariaDB

UPDATE books AS b
  JOIN authors AS a
      ON a.author_id = b.author_id
  SET b.price = b.price * (1 - a.discount_rate);
  • UPDATE ... LIMIT. Supported by MySQL, MariaDB, and SQLite. PostgreSQL, SQL Server, and Oracle Database do not support it directly; use a subquery to restrict the target rows.
  • RETURNING. PostgreSQL, Oracle Database, and modern SQLite/MariaDB support UPDATE ... RETURNING, which returns the updated rows in the same statement. SQL Server offers the OUTPUT clause with similar semantics.
  • Boolean and date literals. The DATE '2000-01-01' form is portable to most modern databases, but Oracle Database users sometimes prefer TO_DATE(...).

When in doubt, consult the documentation for the database and version you are using.

Best Practices

  • Always test the WHERE clause with SELECT first. Confirm the exact set of rows before modifying them.
  • Never rely on the absence of a WHERE clause. If you truly want to change every row, say so explicitly with a comment and a code review.
  • Update only the columns that must change. Leaving other columns out of the SET list prevents accidental overwrites.
  • Use parameters, not string concatenation. This prevents SQL injection and improves plan reuse in application code.
  • Wrap related updates in a transaction. If two changes must be consistent with each other, they belong in one transaction with an explicit COMMIT or ROLLBACK.
  • Check the affected-row count. In application code, verify that the number of updated rows matches your expectation.
  • Handle NULL explicitly. Use IS NULL and IS NOT NULL in the WHERE clause when a column can be missing.
  • Prefer readable expressions. price = price * 0.90 communicates a 10% discount more clearly than a precomputed constant.

Conclusion

You learned how to use the SQL UPDATE statement to modify existing rows, how to change multiple columns at once, how to use expressions that reference the current value, and how database-specific extensions can make cross-table updates more readable. Above all, remember that an UPDATE without a WHERE clause changes every row in the table — always verify the target rows with a SELECT first, and wrap related changes in a transaction when consistency matters.

How do I find unique values using DISTINCT?

By Wayan in Learning SQL 0 Comment

When you query a table, the result set often contains repeated values. For example, if you list the countries of every customer, the same country name appears many times. The DISTINCT keyword tells the database to return only unique values, removing duplicates from the result.

In this tutorial you will learn what DISTINCT does, how to apply it to one or several columns, how it interacts with NULL, and when to prefer alternatives such as GROUP BY.

Prerequisites

To follow along you should be comfortable with:

  • writing a basic SELECT statement;
  • filtering rows with WHERE;
  • running SQL against a local database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).

If you are new to SELECT, review a beginner SELECT tutorial first.

Sample Database

We will use a small online bookstore schema. Create the table and insert the sample data below.

CREATE TABLE books (
    book_id      INTEGER      PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    author_name  VARCHAR(100) NOT NULL,
    genre        VARCHAR(50),
    language     VARCHAR(30)  NOT NULL,
    published_year INTEGER
);

INSERT INTO books (book_id, title, author_name, genre, language, published_year) VALUES
    (1, 'The Silent River',       'Anna Novak',      'Fiction',   'English', 2019),
    (2, 'Cooking with Herbs',     'Marco Bianchi',   'Cooking',   'English', 2021),
    (3, 'Distant Galaxies',       'Anna Novak',      'Science',   'English', 2022),
    (4, 'La Cucina Italiana',     'Marco Bianchi',   'Cooking',   'Italian', 2020),
    (5, 'Silent Mountains',       'Chen Wei',        'Fiction',   'English', 2019),
    (6, 'Introduction to Botany', 'Priya Sharma',    'Science',   'English', 2018),
    (7, 'Recipes from Home',      'Marco Bianchi',   'Cooking',   'Italian', 2023),
    (8, 'Untitled Draft',         'Anna Novak',       NULL,       'English', 2024),
    (9, 'Notes on Rivers',        'Chen Wei',         NULL,       'English', 2020);

Basic Syntax

Place DISTINCT immediately after SELECT:

SELECT DISTINCT column_name
FROM table_name;
  • DISTINCT applies to the entire row produced by the SELECT list, not to a single column in isolation.
  • When you list multiple columns, uniqueness is evaluated across the whole combination.

Practical Example

Suppose the marketing team wants a list of every genre carried by the bookstore, without duplicates.

SELECT DISTINCT genre
FROM books;

The database reads every row in books, extracts the genre value, and returns each unique value once.

Expected Result

genre
Fiction
Cooking
Science
NULL

Two rows contain NULL in genre, but DISTINCT collapses them into a single NULL in the output. DISTINCT treats NULL values as equal to each other for the purpose of removing duplicates, even though NULL = NULL is not true in general SQL comparisons.

Additional Examples

DISTINCT on Multiple Columns

Uniqueness is calculated across the combination of listed columns.

SELECT DISTINCT
    author_name,
    language
FROM books;

Expected result:

author_name language
Anna Novak English
Marco Bianchi English
Marco Bianchi Italian
Chen Wei English
Priya Sharma English

Marco Bianchi appears twice because he has books in two different languages. The pair (Marco Bianchi, English) is distinct from (Marco Bianchi, Italian).

Combining DISTINCT with WHERE

Filter rows first, then remove duplicates:

SELECT DISTINCT author_name
FROM books
WHERE genre = 'Cooking';

Expected result:

author_name
Marco Bianchi

Combining DISTINCT with ORDER BY

You can sort the unique values. Every column in ORDER BY must also appear in the SELECT list when using DISTINCT.

SELECT DISTINCT genre
FROM books
WHERE genre IS NOT NULL
ORDER BY genre ASC;

Counting Unique Values

To count how many unique values exist, wrap the column in COUNT(DISTINCT ...):

SELECT COUNT(DISTINCT author_name) AS unique_authors
FROM books;

Expected result:

unique_authors
4

Note that COUNT(DISTINCT column_name) ignores NULL values, which is different from how DISTINCT behaves in a SELECT list.

DISTINCT vs. GROUP BY

The following two queries produce the same set of unique genres:

SELECT DISTINCT genre
FROM books;

SELECT genre
FROM books
GROUP BY genre;

Prefer DISTINCT when you only need unique values. Prefer GROUP BY when you also need aggregate values per group, such as counts or sums:

SELECT
    genre,
    COUNT(*) AS book_count
FROM books
GROUP BY genre;

Common Mistakes

Applying DISTINCT to One Column Expecting per-Column Uniqueness

Incorrect assumption:

SELECT DISTINCT author_name, title
FROM books;

Readers sometimes expect this to return one row per unique author_name. It does not. DISTINCT considers the entire selected row, and every title is unique, so no rows are collapsed.

If you want one author per row, select only author_name:

SELECT DISTINCT author_name
FROM books;

Using DISTINCT to Hide a Faulty JOIN

Adding DISTINCT to remove duplicate rows caused by an incorrect join often masks a real problem. Instead, review the join condition, the relationships between tables, and whether a one-to-many relationship is producing the extra rows.

Assuming DISTINCT Sorts the Result

DISTINCT does not guarantee any specific ordering. If the order matters, add an explicit ORDER BY clause.

Database Compatibility

SELECT DISTINCT is part of standard SQL and works in:

  • PostgreSQL;
  • MySQL;
  • MariaDB;
  • SQL Server;
  • Oracle Database;
  • SQLite.

PostgreSQL additionally supports a non-standard extension, DISTINCT ON, which keeps the first row per group according to ORDER BY:

SELECT DISTINCT ON (author_name)
    author_name,
    title,
    published_year
FROM books
ORDER BY author_name, published_year DESC;

This returns the most recently published book per author. DISTINCT ON is not portable, so avoid it in code that must run on other databases.

Best Practices

  • List only the columns you need. DISTINCT on many columns is expensive because the database must compare every column value.
  • Filter first with WHERE. Reducing the row set before applying DISTINCT lowers the work required.
  • Prefer GROUP BY when you need aggregates. Do not combine DISTINCT with GROUP BY unless you have a specific reason.
  • Add ORDER BY when order matters. DISTINCT does not sort.
  • Measure performance on large tables. DISTINCT may require sorting or hashing the entire result. Inspect the execution plan with EXPLAIN to understand the cost. The exact syntax and output of EXPLAIN vary between database systems.
  • Handle NULL deliberately. Remember that DISTINCT treats all NULL values as one group, while COUNT(DISTINCT column_name) ignores them entirely.

Conclusion

You learned how to use the SQL DISTINCT keyword to remove duplicate rows from a result set, how it applies to one or more columns, how it interacts with NULL, and how it compares to GROUP BY. Use DISTINCT when you need a clean list of unique values, and switch to GROUP BY when you also need aggregate information per group.

How do I limit the number of rows returned by a query?

By Wayan in Learning SQL 0 Comment

A SELECT statement can easily return thousands or millions of rows, but in most real applications you only need a handful of them at a time — the ten cheapest books, the top five customers, the most recent orders, or a single page of search results. Downloading everything and discarding the rest wastes memory, network bandwidth, and time.

SQL provides a way to tell the database, “return only the first N rows of the result.” The keyword is not the same in every product — you will see LIMIT, TOP, and FETCH FIRST — but the idea is identical. In this tutorial you will learn how to limit rows portably, how to combine limiting with ORDER BY, how to page through a large result set with OFFSET, and which dialect uses which keyword.

Prerequisites

To follow along, you need:

  • A working SQL database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite).
  • A client where you can execute SQL statements, such as DBeaver, psql, mysql, or sqlite3.
  • Basic familiarity with SELECT, WHERE, and ORDER BY.

If you do not have a sample database yet, the setup script in the next section creates everything the tutorial needs.

Sample Database

We reuse the small online bookstore schema from the previous tutorials in this series. It contains two tables: authors and books.

CREATE TABLE authors
(
    author_id   INTEGER PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country     VARCHAR(50)
);

CREATE TABLE books
(
    book_id      INTEGER PRIMARY KEY,
    title        VARCHAR(200)  NOT NULL,
    author_id    INTEGER       NOT NULL,
    category     VARCHAR(50),
    price        DECIMAL(8, 2) NOT NULL,
    stock        INTEGER       NOT NULL,
    published_on DATE,
    CONSTRAINT fk_books_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO authors (author_id, author_name, country)
VALUES (1, 'Jane Austen', 'United Kingdom'),
       (2, 'Haruki Murakami', 'Japan'),
       (3, 'Chimamanda Ngozi Adichie', 'Nigeria'),
       (4, 'Gabriel Garcia Marquez', 'Colombia');

INSERT INTO books (book_id, title, author_id, category, price, stock,
                   published_on)
VALUES (1, 'Pride and Prejudice', 1, 'Classic', 12.50, 20, '1813-01-28'),
       (2, 'Emma', 1, 'Classic', 10.00, 0, '1815-12-23'),
       (3, 'Norwegian Wood', 2, 'Fiction', 15.75, 12, '1987-09-04'),
       (4, 'Kafka on the Shore', 2, 'Fiction', 18.20, 5, '2002-09-12'),
       (5, 'Half of a Yellow Sun', 3, 'Historical', 16.00, 8, '2006-08-11'),
       (6, 'Americanah', 3, 'Contemporary', 14.50, 3, '2013-05-14'),
       (7, 'One Hundred Years of Solitude', 4, 'Classic', 22.00, 25,
        '1967-05-30'),
       (8, 'Love in the Time of Cholera', 4, 'Classic', 19.99, 0, '1985-09-05');

Eight books is enough to make the effect of row-limiting easy to see and check by hand.

Basic Syntax

Row limiting is written in different ways depending on the database, but every dialect requires two things to be useful:

  1. A defined order for the rows (via ORDER BY), so that “the first N rows” has a meaningful answer.
  2. A limit value — the maximum number of rows to return.

The three main forms are shown below.

PostgreSQL, MySQL, MariaDB, SQLite

SELECT column_list
FROM table_name
ORDER BY sort_expression LIMIT row_count;

SQL Server

SELECT TOP(row_count)
       column_list
FROM table_name
ORDER BY sort_expression;

Oracle Database and ANSI SQL

SELECT column_list
FROM table_name
ORDER BY sort_expression
    FETCH FIRST row_count ROWS ONLY;

FETCH FIRST ... ROWS ONLY is defined by the SQL standard and is also supported by PostgreSQL and modern versions of MySQL, MariaDB, and SQL Server. When portability matters, prefer this form.

Important: without an ORDER BY clause, “the first N rows” is not really defined. The database is free to return any N rows it wants, and that choice can change between runs. Always combine row limiting with an explicit order when the answer must be meaningful.

Practical Example

The bookstore manager wants to display the three cheapest books on the home page.

SELECT book_id,
       title,
       price
FROM books
ORDER BY price ASC LIMIT 3;

Reading the query in logical execution order:

  1. FROM books — start from every row in the books table.
  2. ORDER BY price ASC — sort the rows from the smallest to the largest price.
  3. LIMIT 3 — keep only the first three rows of that sorted result.
  4. SELECT book_id, title, price — return the three columns for those rows.

Expected Result

book_id title price
2 Emma 10.00
1 Pride and Prejudice 12.50
6 Americanah 14.50

Additional Examples

1. Top N in Descending Order

To show the three most expensive books, keep the same LIMIT and reverse the sort direction.

SELECT book_id,
       title,
       price
FROM books
ORDER BY price DESC LIMIT 3;

Expected result:

book_id title price
7 One Hundred Years of Solitude 22.00
8 Love in the Time of Cholera 19.99
4 Kafka on the Shore 18.20

2. Portable Syntax with FETCH FIRST

The same result written in ANSI-standard form:

SELECT book_id,
       title,
       price
FROM books
ORDER BY price DESC
    FETCH FIRST 3 ROWS ONLY;

This runs on PostgreSQL, Oracle Database, SQL Server 2012+, MySQL 8.0.31+, MariaDB 10.6+, and SQLite 3.30+.

3. SQL Server TOP

SQL Server uses TOP in the SELECT list. Parentheses around the count are recommended and required when the count is an expression or parameter.

SELECT TOP(3)
       book_id,
       title,
       price
FROM books
ORDER BY price DESC;

4. Pagination with OFFSET

To display page 2 of a list where each page shows three books, skip the first three rows and take the next three.

-- PostgreSQL, MySQL, MariaDB, SQLite
SELECT book_id,
       title,
       price
FROM books
ORDER BY price ASC, book_id ASC LIMIT 3
OFFSET 3;

Portable version:

SELECT book_id,
       title,
       price
FROM books
ORDER BY price ASC, book_id ASC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

Expected result (rows 4 through 6 of the price-ascending list):

book_id title price
3 Norwegian Wood 15.75
5 Half of a Yellow Sun 16.00
4 Kafka on the Shore 18.20

Notice the tie-breaker book_id ASC in the ORDER BY. Without it, two books that share the same price could switch positions between page 1 and page 2, so a row might appear twice or not at all.

5. Limiting After Filtering

Row limiting is applied after WHERE, so you can safely combine both.

SELECT title,
       price,
       stock
FROM books
WHERE category = 'Classic'
  AND stock > 0
ORDER BY price ASC LIMIT 2;

“Show me the two cheapest classic books that are still in stock.”

Expected result:

title price stock
Pride and Prejudice 12.50 20
One Hundred Years of Solitude 22.00 25

6. Sampling Without an ORDER BY

Sometimes you genuinely do not care which rows you get — you just want a small, cheap sample while exploring a large table:

SELECT book_id,
       title
FROM books LIMIT 5;

This is fine for ad-hoc inspection, but do not rely on the row order or on which rows you get. For anything a user will see, add an ORDER BY.

7. Using LIMIT from Java

When your application shows one page of results at a time, pass the page size and offset as parameters — never build the SQL by concatenating them.

String sql = """
    SELECT book_id, title, price
    FROM books
    WHERE category = ?
    ORDER BY price ASC, book_id ASC
    LIMIT ? OFFSET ?
    """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, category);
statement.setInt(2, pageSize);
statement.setInt(3, pageSize * (pageIndex - 1));

    try (ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            long bookId = resultSet.getLong("book_id");
            String title = resultSet.getString("title");
            BigDecimal price = resultSet.getBigDecimal("price");
            // process the row...
        }
    }
}

Parameterizing the values keeps the query cache-friendly and eliminates SQL-injection risk.

Common Mistakes

Using LIMIT Without ORDER BY

Incorrect:

SELECT title, price
FROM books LIMIT 3;
-- "Give me the three cheapest books."

The query does not ask for the cheapest books — it asks for any three. The database is free to return whichever three rows are convenient. Add the sort:

SELECT title, price
FROM books
ORDER BY price ASC LIMIT 3;

Forgetting a Tie-Breaker When Paging

If several rows share the same value in the sort column, their relative order is undefined. During pagination this can cause rows to appear twice or be skipped.

Fragile:

SELECT book_id, title, price
FROM books
ORDER BY price ASC LIMIT 3
OFFSET 3;

Reliable:

SELECT book_id, title, price
FROM books
ORDER BY price ASC, book_id ASC LIMIT 3
OFFSET 3;

Mixing Dialects in the Same Statement

SELECT TOP 3 ... LIMIT 3 is not valid SQL in any product. Choose one form based on your database:

  • PostgreSQL, MySQL, MariaDB, SQLite → LIMIT (or FETCH FIRST).
  • SQL Server → TOP (or OFFSET ... FETCH NEXT).
  • Oracle Database → FETCH FIRST (or ROWNUM in older versions).

Assuming LIMIT Runs Before WHERE

LIMIT is applied to the already-filtered, already-sorted result set, not to the raw table. LIMIT 10 does not mean “read only 10 rows from disk” — the database may still scan the whole table if there is no supporting index. Use WHERE to reduce the working set, and consider indexes on the filter and sort columns for large tables.

Database Compatibility

Database Preferred syntax
PostgreSQL LIMIT n [OFFSET m] or FETCH FIRST n ROWS ONLY
MySQL LIMIT n [OFFSET m] or LIMIT m, n
MariaDB LIMIT n [OFFSET m] or LIMIT m, n
SQLite LIMIT n [OFFSET m]
SQL Server SELECT TOP (n) ... or OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle Database FETCH FIRST n ROWS ONLY (12c+); ROWNUM <= n on older versions

Notes:

  • FETCH FIRST / FETCH NEXT is part of ANSI SQL and is the most portable option across modern versions.
  • LIMIT m, n (MySQL and MariaDB shorthand) treats the first value as the offset and the second as the count. This ordering is easy to mix up; prefer LIMIT n OFFSET m for clarity.
  • Older Oracle Database versions (pre-12c) do not support FETCH FIRST. Use WHERE ROWNUM <= n around a sorted subquery.
  • SQL Server requires an ORDER BY clause when using OFFSET ... FETCH NEXT.

When in doubt, consult the documentation for the specific database and version you are using.

Best Practices

  • Combine row limiting with an explicit ORDER BY whenever the identity of the returned rows matters.
  • Add a deterministic tie-breaker, such as the primary key, so that ordering and pagination remain stable across runs.
  • Prefer the portable FETCH FIRST n ROWS ONLY form when writing SQL that must run on more than one database.
  • Use parameters for the limit and offset values, especially in application code — do not concatenate them into the SQL string.
  • Consider indexes on filter and sort columns for large tables, and measure with your database’s execution-plan tool before assuming a benefit.
  • Be careful with very large OFFSET values. Most databases still read and discard all the skipped rows, so deep pagination can be surprisingly slow. Keyset pagination — filtering with WHERE (sort_key, id) > (last_sort_key, last_id) — is often faster for large data sets.

Conclusion

You learned how to restrict the number of rows a query returns using LIMIT, TOP, and FETCH FIRST, how to combine row limiting with ORDER BY and OFFSET for pagination, and how the syntax differs across PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, and SQLite. Always sort explicitly and add a deterministic tie-breaker before you rely on “top N” or paged results.