How do I sort query results using ORDER BY?

By Wayan in SQL 0 Comment

When you run a SELECT statement, the database does not promise to return rows in any particular order. If you want a predictable sequence — the cheapest books first, the newest orders on top, or authors listed alphabetically — you have to ask for it explicitly. The tool SQL gives you for that is the ORDER BY clause.

In this tutorial you will learn how to sort rows in ascending or descending order, sort by more than one column, sort by expressions and aliases, and control where NULL values appear. You will also see the most common mistakes and how each dialect handles the differences.

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 (a GUI tool like DBeaver, or a command-line client).
  • Basic familiarity with the SELECT and WHERE clauses.

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 used in the previous tutorial. 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'),
    (9, 'Unknown Title',                 2, NULL,           13.00,  4, NULL);

The last row contains NULL values for category and published_on. We will use it to see how ORDER BY treats missing data.

Basic Syntax

The ORDER BY clause appears after WHERE (and after GROUP BY / HAVING when they are present), and before LIMIT.

SELECT column_list
FROM table_name
WHERE condition
ORDER BY sort_expression [ASC | DESC] [, sort_expression [ASC | DESC] ...];
  • sort_expression is usually a column name, but it can also be an expression, an alias, or a column position number.
  • ASC sorts from smallest to largest and is the default.
  • DESC sorts from largest to smallest.
  • Without ORDER BY, the returned row order is undefined, even if it looks stable during testing.

Practical Example

The bookstore manager wants a price list of every book, from the cheapest to the most expensive.

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

Reading the query in logical order:

  1. FROM books — start with every row in the books table.
  2. SELECT book_id, title, price — pick the three columns to return.
  3. ORDER BY price ASC — sort the result set by price from lowest to highest.

Expected Result

book_id title price
2 Emma 10.00
1 Pride and Prejudice 12.50
9 Unknown Title 13.00
6 Americanah 14.50
3 Norwegian Wood 15.75
5 Half of a Yellow Sun 16.00
4 Kafka on the Shore 18.20
8 Love in the Time of Cholera 19.99
7 One Hundred Years of Solitude 22.00

Additional Examples

1. Descending Order

Use DESC when the largest value should come first, for example a “most expensive first” list.

SELECT
    title,
    price
FROM books
ORDER BY price DESC;

2. Sorting by Multiple Columns

You can sort by more than one column. Each column can independently use ASC or DESC. The database sorts by the first expression, then breaks ties with the second, and so on.

SELECT
    category,
    title,
    price
FROM books
WHERE category IS NOT NULL
ORDER BY
    category ASC,
    price    DESC;

“Group books by category alphabetically, and within each category show the most expensive book first.”

Expected result:

category title price
Classic One Hundred Years of Solitude 22.00
Classic Love in the Time of Cholera 19.99
Classic Pride and Prejudice 12.50
Classic Emma 10.00
Contemporary Americanah 14.50
Fiction Kafka on the Shore 18.20
Fiction Norwegian Wood 15.75
Historical Half of a Yellow Sun 16.00

3. Sorting by an Expression

ORDER BY accepts any expression that the database can evaluate — arithmetic, string operations, function calls, and so on.

SELECT
    title,
    price,
    stock,
    price * stock AS inventory_value
FROM books
ORDER BY price * stock DESC;

The rows are ordered by the computed inventory value even though the raw price and stock columns are not sorted individually.

4. Sorting by an Alias

Most databases allow you to use the alias defined in the SELECT list, because ORDER BY is logically evaluated after SELECT.

SELECT
    title,
    price * stock AS inventory_value
FROM books
ORDER BY inventory_value DESC;

This is often more readable than repeating the full expression. Availability varies slightly — see the Database Compatibility section for details.

5. Sorting Strings

String columns are sorted according to the column’s collation, which controls case sensitivity and locale-aware rules.

SELECT
    author_name,
    country
FROM authors
ORDER BY author_name ASC;

Expected result:

author_name country
Chimamanda Ngozi Adichie Nigeria
Gabriel Garcia Marquez Colombia
Haruki Murakami Japan
Jane Austen United Kingdom

If your data mixes upper- and lower-case letters and the ordering surprises you, check the collation for your column or database.

6. Sorting Dates

Dates and timestamps sort chronologically. ASC produces oldest-first; DESC produces newest-first.

SELECT
    title,
    published_on
FROM books
WHERE published_on IS NOT NULL
ORDER BY published_on DESC;

7. Controlling NULL Placement

NULL values need special treatment because they are neither less than nor greater than any real value. Different databases place them in different positions by default:

  • PostgreSQL and Oracle Database place NULL values last in ASC order and first in DESC order.
  • MySQL, MariaDB, and SQLite place NULL values first in ASC order and last in DESC order.
  • SQL Server places NULL values first in ASC order and last in DESC order.

ANSI SQL defines the optional NULLS FIRST and NULLS LAST modifiers to make the placement explicit.

-- PostgreSQL, Oracle Database, SQLite 3.30+
SELECT
    title,
    published_on
FROM books
ORDER BY published_on ASC NULLS LAST;

If your database does not support NULLS FIRST / NULLS LAST, you can emulate them with a helper expression:

-- Portable emulation: put NULLs last in ascending order
SELECT
    title,
    published_on
FROM books
ORDER BY
    CASE WHEN published_on IS NULL THEN 1 ELSE 0 END,
    published_on ASC;

8. Combining ORDER BY with LIMIT

ORDER BY is what makes LIMIT meaningful. Asking for the “top 3” without sorting first is not really a top-3 query.

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

Equivalent examples for other systems:

-- SQL Server
SELECT TOP 3
    title,
    price
FROM books
ORDER BY price DESC;

-- Oracle Database and ANSI SQL
SELECT
    title,
    price
FROM books
ORDER BY price DESC
FETCH FIRST 3 ROWS ONLY;

9. Using ORDER BY in Application Code

When your application performs paging or user-selected sorting, keep the sort expression on the server side and pass only the values you actually want to filter by as parameters.

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

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, category);

    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...
        }
    }
}

Never build the ORDER BY clause by concatenating user input directly into the SQL string. If users may choose the sort column, validate the incoming value against a fixed allow-list of column names before assembling the query.

Common Mistakes

Assuming a Query Is Sorted Without ORDER BY

Incorrect:

SELECT title, price
FROM books;
-- "The rows come back sorted by book_id anyway."

The order you happen to observe is an artifact of the current storage layout, statistics, and execution plan. It can change silently after an update, an index change, or an upgrade. If order matters, always request it:

SELECT title, price
FROM books
ORDER BY book_id;

Sorting by Column Position Numbers

Some databases allow ordering by the position of a column in the SELECT list, for example ORDER BY 2. This is fragile — inserting a new column silently changes the sort. Prefer explicit column names or aliases.

Fragile:

SELECT title, price
FROM books
ORDER BY 2 DESC;

Better:

SELECT title, price
FROM books
ORDER BY price DESC;

Forgetting a Tie-Breaker for Deterministic Ordering

If several rows share the same value in the sort column, their relative order is undefined. For paging or reproducible reports, add a unique tie-breaker such as the primary key.

SELECT
    title,
    price
FROM books
ORDER BY
    price   DESC,
    book_id ASC;

Ignoring NULL Placement Differences

If your query is portable across databases, do not rely on the default position of NULL values. Use NULLS FIRST / NULLS LAST where supported, or the CASE-based emulation shown earlier.

Database Compatibility

The core ORDER BY syntax with ASC and DESC is part of ANSI SQL and is supported by:

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

Notable differences you may encounter:

  • NULLS FIRST / NULLS LAST. Supported by PostgreSQL, Oracle Database, and SQLite 3.30+. MySQL, MariaDB, and SQL Server do not accept these keywords; use a CASE expression to control placement.
  • Default NULL position. As described in section 7, defaults differ across engines. When in doubt, be explicit.
  • Row limiting. Combine ORDER BY with LIMIT (PostgreSQL, MySQL, MariaDB, SQLite), TOP (SQL Server), or FETCH FIRST n ROWS ONLY (Oracle Database and ANSI SQL).
  • Sorting by alias. PostgreSQL, MySQL, MariaDB, SQLite, and SQL Server accept SELECT-list aliases in ORDER BY. Oracle Database also accepts them in most cases, but repeating the expression is always safe.
  • Collation and case sensitivity. String ordering depends on the column’s collation. Two databases with different default collations can order the same data differently.

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

Best Practices

  • Always sort explicitly when order matters. The database is free to return rows in any order otherwise.
  • Add a deterministic tie-breaker. A unique column such as the primary key guarantees stable ordering, especially with LIMIT or paging.
  • Prefer column names over positions. Ordering by 1, 2, 3 breaks silently when the SELECT list changes.
  • Be explicit about NULL placement when writing portable SQL.
  • Sort on indexed columns when possible. An index on the sort column can let the database skip an expensive sort step. Measure with your database’s execution-plan tool before assuming a benefit.
  • Sort in the database, not in the application. The database can use indexes and streaming; loading everything into memory to sort in Java is slower and wastes bandwidth.
  • Validate user-supplied sort keys against an allow-list. Never inject raw user input into an ORDER BY clause.

Conclusion

You learned how to use the SQL ORDER BY clause to sort query results in ascending or descending order, sort by multiple columns and expressions, and control how NULL values are positioned. You also saw why a deterministic tie-breaker matters for paging and how dialects differ in NULL handling and row limiting.

How do I filter SQL query results using WHERE?

By Wayan in SQL 0 Comment

When you query a table, you rarely want every row it contains. Most of the time you want specific rows — customers from a certain city, orders above a certain price, or books written by a particular author. The WHERE clause is the tool SQL gives you to express exactly which rows should be returned.

In this tutorial you will learn how to use WHERE with comparison operators, logical operators, ranges, lists, pattern matching, and NULL checks. You will also see the most common mistakes beginners make and how to avoid them.

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 (a GUI tool like DBeaver, or a command-line client).
  • Basic familiarity with the SELECT statement.

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

Sample Database

Throughout this tutorial we 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) 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);

The final row uses NULL for both category and published_on on purpose — we will use it to demonstrate how WHERE handles missing values.

Basic Syntax

The WHERE clause appears after FROM and before clauses like ORDER BY or GROUP BY.

SELECT column_list
FROM table_name
WHERE condition;
  • condition is a boolean expression evaluated once per row.
  • A row is included in the result only when the condition evaluates to true.
  • Rows that evaluate to false or unknown (which is what NULL comparisons return) are excluded.

Practical Example

Suppose the bookstore manager wants to see every book that costs more than 15.00.

SELECT
    book_id,
    title,
    price
FROM books
WHERE price > 15.00;

Reading the query in logical order:

  1. FROM books — start with every row in the books table.
  2. WHERE price > 15.00 — keep only rows where the price is greater than 15.00.
  3. SELECT book_id, title, price — return these three columns.

Expected Result

book_id title price
3 Norwegian Wood 15.75
4 Kafka on the Shore 18.20
5 Half of a Yellow Sun 16.00
7 One Hundred Years of Solitude 22.00
8 Love in the Time of Cholera 19.99

Additional Examples

1. Comparison Operators

SQL supports the standard set of comparison operators.

Operator Meaning
= Equal to
<> or != Not equal to
<, > Less than / greater than
<=, >= Less than or equal / etc. 
SELECT title, category
FROM books
WHERE category = 'Classic';

Returns all books whose category is exactly 'Classic'. String comparisons are case-sensitive in some databases (for example, PostgreSQL) and case-insensitive in others (for example, SQL Server with default collation). Consult your database documentation if this matters for your data.

2. Combining Conditions with AND, OR, and NOT

You can combine multiple conditions to express more precise rules.

SELECT title, price, stock
FROM books
WHERE category = 'Classic'
  AND stock > 0;

“Return classic books that are currently in stock.”

Use parentheses when mixing AND and OR — AND has higher precedence than OR, and forgetting this is a common source of bugs.

SELECT title, category, price
FROM books
WHERE (category = 'Classic' OR category = 'Fiction')
  AND price < 20.00;

3. Ranges with BETWEEN

BETWEEN is a readable shortcut for a closed range. Both endpoints are included.

SELECT title, price
FROM books
WHERE price BETWEEN 12.00 AND 16.00;

This is equivalent to:

SELECT title, price
FROM books
WHERE price >= 12.00
  AND price <= 16.00;

4. Lists with IN

Use IN when you want to match any value from a fixed list.

SELECT title, category
FROM books
WHERE category IN ('Classic', 'Fiction');

This is much cleaner than chaining OR conditions.

5. Pattern Matching with LIKE

LIKE matches strings against a pattern. The two standard wildcards are:

  • % — matches any sequence of characters (including none).
  • _ — matches exactly one character.
SELECT title
FROM books
WHERE title LIKE '%Love%';

Returns any title containing the substring Love.

SELECT author_name
FROM authors
WHERE author_name LIKE 'J%';

Returns authors whose name starts with J.

Case sensitivity of LIKE varies between databases. PostgreSQL provides ILIKE for case-insensitive matching; other systems rely on the column’s collation.

6. Handling NULL

NULL is not equal to anything — not even to another NULL. Comparisons involving NULL return unknown, and rows with an unknown result are excluded by WHERE.

To test for missing values, use IS NULL or IS NOT NULL.

SELECT title, published_on
FROM books
WHERE published_on IS NULL;

To exclude rows with a missing category:

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

7. Filtering with Dates

Date comparisons work like number comparisons, but the literal format depends on the database.

SELECT title, published_on
FROM books
WHERE published_on >= DATE '2000-01-01';

The DATE '...' prefix is the ANSI-standard form and is accepted by PostgreSQL, MySQL 8+, MariaDB, and SQLite. SQL Server and Oracle Database accept the plain string '2000-01-01' in most contexts as well.

8. Using WHERE in Application Code

When the filter value comes from user input or another variable, use a parameterized query rather than string concatenation. This prevents SQL injection and lets the database reuse the prepared plan.

String sql = """
        SELECT book_id, title, price
        FROM books
        WHERE category = ?
          AND price < ?
        """;

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, category);
    statement.setBigDecimal(2, maxPrice);

    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...
        }
    }
}

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 on every standard-compliant database because category = NULL evaluates to unknown, never true.

Mixing AND and OR Without Parentheses

Incorrect:

SELECT title, category, price
FROM books
WHERE category = 'Classic' OR category = 'Fiction'
  AND price < 20.00;

Because AND binds tighter than OR, this is interpreted as “all classic books, plus fiction books cheaper than 20.00”, which is probably not what you want.

Correct:

SELECT title, category, price
FROM books
WHERE (category = 'Classic' OR category = 'Fiction')
  AND price < 20.00;

Forgetting WHERE in an UPDATE or DELETE

Before you delete or update rows, verify the filter with a SELECT first.

SELECT *
FROM books
WHERE stock = 0;

Only after confirming the target rows should you run:

DELETE FROM books
WHERE stock = 0;

Warning: Running UPDATE or DELETE 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.

Using <> with NULL

The expression category <> 'Classic' will not return rows where category IS NULL, because comparing NULL to anything yields unknown. If you need those rows too, add an explicit NULL check:

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

Database Compatibility

The core WHERE clause and the operators shown here (=, <>, <, >, <=, >=, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL, IS NOT NULL) are part of ANSI SQL and work in:

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

Notable differences you may encounter:

  • Case sensitivity. String equality and LIKE are case-sensitive in PostgreSQL by default but often case-insensitive in MySQL and SQL Server, depending on collation.
  • Case-insensitive LIKE. PostgreSQL offers ILIKE. Other systems typically rely on collation or functions such as LOWER().
  • Date literals. The DATE '2000-01-01' form is portable to most systems; Oracle Database also accepts it, while some tools prefer TO_DATE('2000-01-01', 'YYYY-MM-DD').
  • Boolean values. PostgreSQL has a native BOOLEAN type; SQL Server and Oracle Database traditionally use BIT or numeric flags.

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

Best Practices

  • List the columns you need. Combine a specific SELECT list with a precise WHERE clause instead of scanning SELECT *.
  • Use parameters, not string concatenation. This prevents SQL injection and improves plan reuse.
  • Handle NULL explicitly. If a column can be NULL, decide whether those rows should be included and add IS NULL / IS NOT NULL accordingly.
  • Parenthesize mixed logical expressions. It makes intent obvious and avoids precedence bugs.
  • Verify destructive filters with SELECT first. Never run an UPDATE or DELETE before you have seen the rows it will affect.
  • Prefer readable operators. BETWEEN and IN communicate intent better than long chains of AND/OR.
  • Beware of functions on filtered columns. Wrapping a column in a function (for example, WHERE LOWER(title) = 'emma') can prevent an index from being used. If this matters, measure with your database’s execution-plan tool before optimizing.

Conclusion

You learned how to use the SQL WHERE clause to filter rows with comparison, logical, range, list, pattern-matching, and NULL conditions. You also saw how operator precedence, three-valued logic, and parameterized queries affect real-world usage. Always verify an UPDATE or DELETE condition with SELECT before modifying data.

How do I retrieve data using a SELECT statement?

By Wayan in SQL 0 Comment

The SELECT statement is the most fundamental SQL command — it lets you read (query) data from one or more tables in a relational database. Here’s a comprehensive guide to using it effectively.

1. The Basic Syntax

SELECT column1, column2, ...
FROM table_name;
  • SELECT — tells the database what columns you want.
  • FROM — tells it where (which table) to look.

Example: Get all names and emails from a users table

SELECT name, email
FROM users;

2. Selecting All Columns with *

Use the asterisk (*) to retrieve every column:

SELECT *
FROM users;

Tip: Avoid SELECT * in production code. It’s slower, returns unnecessary data, and breaks if the schema changes. Always list the columns you actually need.

3. Filtering Rows with WHERE

The WHERE clause restricts which rows are returned.

SELECT name, email
FROM users
WHERE age > 18;

Common WHERE operators

Operator Meaning Example
= Equal status = 'ACTIVE'
<> or != Not equal country <> 'US'
<, >, <=, >= Comparison age >= 21
BETWEEN Within a range age BETWEEN 18 AND 65
IN Matches any value in a list country IN ('US', 'UK', 'DE')
LIKE Pattern match name LIKE 'A%' (starts with “A”)
IS NULL Missing value deleted_at IS NULL

Combining conditions with AND / OR

SELECT id, name
FROM users
WHERE age >= 18 AND country = 'US';

4. Sorting Results with ORDER BY

SELECT name, age
FROM users
ORDER BY age DESC;
  • ASC — ascending (default)
  • DESC — descending

You can sort by multiple columns:

SELECT name, country, age
FROM users
ORDER BY country ASC, age DESC;

5. Limiting Results

Return only the first N rows (syntax varies by database):

-- PostgreSQL, MySQL, SQLite
SELECT name FROM users LIMIT 10;

-- SQL Server
SELECT TOP 10 name FROM users;

-- Oracle / Standard SQL
SELECT name FROM users FETCH FIRST 10 ROWS ONLY;

6. Removing Duplicates with DISTINCT

SELECT DISTINCT country
FROM users;

Returns each unique country only once.

7. Renaming Columns with Aliases (AS)

SELECT
    name       AS full_name,
    age * 12   AS age_in_months
FROM users;

Aliases make output more readable and are useful for computed columns.

8. Aggregating Data

Aggregate functions summarize multiple rows into one:

Function Purpose
COUNT() Number of rows
SUM() Total of a numeric col
AVG() Average
MIN() Smallest value
MAX() Largest value 
SELECT COUNT(*) AS total_users,
       AVG(age) AS average_age
FROM users;

Grouping with GROUP BY

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

Filtering groups with HAVING

WHERE filters rows before grouping; HAVING filters after.

SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

9. Joining Multiple Tables

Retrieve data spread across related tables using JOIN.

SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100
ORDER BY o.created_at DESC;

Common join types:

  • INNER JOIN — only matching rows in both tables (default)
  • LEFT JOIN — all rows from the left table + matches from the right
  • RIGHT JOIN — the opposite of LEFT
  • FULL OUTER JOIN — all rows from both sides

10. The Logical Order of a SELECT

Even though you write clauses in this order:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

The database executes them in this order:

  1. FROM (and JOIN)
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Understanding this order helps explain why you can’t use a SELECT alias in WHERE, but you can in ORDER BY.

11. A Complete Realistic Example

SELECT
    u.country,
    COUNT(o.id)       AS order_count,
    SUM(o.total)      AS total_revenue,
    AVG(o.total)      AS avg_order_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'COMPLETED'
  AND o.created_at >= '2025-01-01'
GROUP BY u.country
HAVING SUM(o.total) > 10000
ORDER BY total_revenue DESC
LIMIT 20;

“Show me the top 20 countries by revenue in 2025, but only those exceeding $10,000 in completed orders.”

12. Using SELECT in Java (Spring Data JPA)

If you are using Spring Data JPA, you typically don’t execute raw SQL. Instead:

Derived query methods

List<User> findByAgeGreaterThan(int age);
List<User> findByCountryAndStatusOrderByCreatedAtDesc(String country, Status status);

JPQL with @Query

@Query("SELECT u FROM User u WHERE u.age > :minAge")
List<User> findAdults(@Param("minAge") int minAge);

Native SQL with @Query(nativeQuery = true)

@Query(value = "SELECT * FROM users WHERE age > :minAge", nativeQuery = true)
List<User> findAdultsNative(@Param("minAge") int minAge);

Projections (return only certain fields)

public interface UserSummary {
    String getName();
    String getEmail();
}

@Query("SELECT u.name AS name, u.email AS email FROM User u WHERE u.age > :minAge")
List<UserSummary> findAdultSummaries(@Param("minAge") int minAge);

13. Best Practices

  • List columns explicitly instead of SELECT *.
  • Use parameter binding (? placeholders or :named params) — never concatenate user input into SQL (prevents SQL injection).
  • Filter early with WHERE to reduce the working set.
  • Index columns used in WHERE, JOIN, and ORDER BY.
  • Use LIMIT when you only need a few rows.
  • Read the execution plan (EXPLAIN / EXPLAIN ANALYZE) when queries are slow.

Summary

Clause Purpose
SELECT Which columns to return
FROM Which table(s) to read from
JOIN Combine rows from related tables
WHERE Filter individual rows
GROUP BY Group rows sharing a value
HAVING Filter groups
ORDER BY Sort the results
LIMIT Restrict how many rows are returned

How do I insert data into a SQL table?

By Wayan in SQL 0 Comment

To insert data into a SQL table, you use the INSERT INTO statement. Here’s a breakdown of the syntax, common patterns, and how it fits into a Spring Data JPA project.

1. Basic Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example — inserting a user into a users table:

INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 25);

2. Inserting Without Specifying Columns

If you provide values for every column in the exact order they’re defined in the table, you can omit the column list:

INSERT INTO users
VALUES (1, 'Alice', '[email protected]', 25);

Not recommended — if the schema changes, your query breaks. Always list columns explicitly.

3. Inserting Multiple Rows at Once

Most databases (MySQL, PostgreSQL, SQL Server) support multi-row inserts:

INSERT INTO users (name, email, age)
VALUES
  ('Alice', '[email protected]', 25),
  ('Bob',   '[email protected]',   30),
  ('Carol', '[email protected]', 22);

This is much faster than running many separate INSERT statements.

4. Inserting from Another Table

You can copy rows from one table into another using INSERT INTO ... SELECT:

INSERT INTO archived_users (name, email, age)
SELECT name, email, age
FROM users
WHERE age < 18;

5. Handling Auto-Generated Columns

If a column is auto-generated (e.g., id BIGINT AUTO_INCREMENT PRIMARY KEY), simply omit it — the database will generate the value:

INSERT INTO users (name, email, age)
VALUES ('Dave', '[email protected]', 40);

6. Handling Duplicates

Different databases offer different ways to handle conflicts:

PostgreSQL — ON CONFLICT:

INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) DO NOTHING;

MySQL — ON DUPLICATE KEY UPDATE:

INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

7. In a Spring Data JPA Project

In a project that uses Spring Data JPA, you usually don’t write INSERT statements directly. Instead:

Option A — Use the repository (recommended)

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    private int age;
    // getters/setters or Lombok @Data
}

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    public User createUser(String name, String email, int age) {
        User user = new User();
        user.setName(name);
        user.setEmail(email);
        user.setAge(age);
        return userRepository.save(user); // Generates the INSERT for you
    }
}

Option B — Native SQL with @Query

public interface UserRepository extends JpaRepository<User, Long> {

    @Modifying
    @Transactional
    @Query(value = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)",
           nativeQuery = true)
    void insertUser(@Param("name") String name,
                    @Param("email") String email,
                    @Param("age") int age);
}

8. Best Practices

  • Always list columns explicitly — makes queries resilient to schema changes.
  • Use parameterized queries / prepared statements to prevent SQL injection.
  • Batch inserts when loading large amounts of data.
  • Wrap multiple inserts in a transaction for atomicity.
  • Don’t insert into auto-generated columns manually unless you have a reason.

Summary

Task Statement
Insert one row INSERT INTO t (cols) VALUES (...);
Insert multiple rows INSERT INTO t (cols) VALUES (...), (...);
Copy from another table INSERT INTO t (cols) SELECT ... FROM other;
Handle duplicates ON CONFLICT (Postgres) / ON DUPLICATE KEY (MySQL)

How do I choose the correct SQL data type for a column?

By Wayan in SQL 0 Comment

Choosing the right data type is one of the most important decisions when designing a database schema. It affects storage size, performance, data integrity, and future maintainability. Here’s a practical guide to help you decide.

1. Start With the Nature of the Data

Ask yourself: What kind of value will this column hold?

Data Nature Typical SQL Types
Whole numbers SMALLINT, INT, BIGINT
Decimal / monetary DECIMAL(p,s) / NUMERIC(p,s)
Approximate numbers REAL, DOUBLE PRECISION, FLOAT
Short text VARCHAR(n), CHAR(n)
Long text TEXT, CLOB
Dates & times DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE
Boolean flags BOOLEAN (or BIT / TINYINT in some DBs)
Binary data BLOB, BYTEA, VARBINARY
Identifiers (UUIDs) UUID (Postgres), CHAR(36), BINARY(16)
Structured JSON JSON, JSONB (Postgres)

2. Match the Range and Precision

Pick the smallest type that safely fits your data — but don’t over-optimize prematurely.

Integers

  • SMALLINT → -32,768 to 32,767 (age, small counters)
  • INT → ~±2.1 billion (most IDs, counts)
  • BIGINT → for very large IDs or high-volume tables

Decimals

  • Use DECIMAL(p, s) for money and anything requiring exact arithmetic.
price DECIMAL(10, 2)  -- up to 99,999,999.99
  • Avoid FLOAT/DOUBLE for financial data — rounding errors will bite you.

Strings

  • Use VARCHAR(n) when you know a reasonable maximum length.
  • Use TEXT for free-form or unbounded text (descriptions, comments).
  • Use CHAR(n) only for truly fixed-length values (e.g., ISO country codes CHAR(2)).

3. Prefer Semantic Types Over Generic Ones

If your database offers a specialized type, use it — it enforces integrity and enables optimizations.

  • DATE instead of VARCHAR for dates
  • BOOLEAN instead of CHAR(1) with 'Y'/'N'
  • UUID instead of VARCHAR(36)
  • INET / CIDR for IP addresses (Postgres)
  • JSONB instead of TEXT for JSON payloads (Postgres)

4. Consider Time Zones for Timestamps

  • TIMESTAMP → stores no time zone; ambiguous across regions.
  • TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ in Postgres) → recommended for anything user-facing or distributed.
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()

5. Think About NULL vs NOT NULL and Defaults

The type alone isn’t enough — pair it with proper constraints:

email       VARCHAR(255) NOT NULL,
status      VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
deleted_at  TIMESTAMP WITH TIME ZONE NULL

6. Watch Out for Common Pitfalls

Anti-pattern Better choice
FLOAT for money DECIMAL(p, s)
VARCHAR for dates DATE / TIMESTAMP
TEXT for everything VARCHAR(n) with a sane limit
CHAR(1) 'Y'/'N' BOOLEAN
INT for phone numbers VARCHAR(20) (leading zeros, +, formatting)
VARCHAR(255) reflexively Choose a length that reflects the domain

7. Align With Your Application Layer

Keep the SQL type consistent with the Java field type:

Java/Kotlin Recommended SQL
Long / Int BIGINT / INT
BigDecimal DECIMAL(p, s)
String VARCHAR(n) or TEXT
LocalDate DATE
LocalDateTime TIMESTAMP
OffsetDateTime / Instant TIMESTAMP WITH TIME ZONE
UUID UUID (or BINARY(16))
Boolean BOOLEAN
enum VARCHAR (with @Enumerated(EnumType.STRING))

8. A Quick Decision Checklist

Before finalizing a column type, ask:

  1. What values will it hold, and what’s the realistic range?
  2. Exact or approximate arithmetic required?
  3. Fixed or variable length?
  4. Time-zone aware or not?
  5. Does the DB offer a native type (JSON, UUID, INET, etc.)?
  6. Does it match the application layer type cleanly?
  7. Is the column indexed / searched / joined on? Smaller types = faster indexes.
  8. Will it be NULLable? What’s the default?

TL;DR

Pick the most specific, smallest, semantically correct type that fits the data — and align it with both your business rules and your ORM mappings.