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 rightRIGHT JOIN— the opposite of LEFTFULL 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:
FROM(andJOIN)WHEREGROUP BYHAVINGSELECTORDER BYLIMIT
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:namedparams) — never concatenate user input into SQL (prevents SQL injection). - Filter early with
WHEREto reduce the working set. - Index columns used in
WHERE,JOIN, andORDER BY. - Use
LIMITwhen 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 |