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