MySQL SELECT Statement

Last updated: October 19, 2026Oct 19, 2026

SELECT is the statement you'll write more than any other: it reads data from tables and returns a result set. It is the "R" (Read) in CRUD. This tutorial starts with the simplest SELECT and builds up step by step, using a small table of trips.

1. Sample Data

Table: trips (8 rows)

trip_id	traveller	city	country	nights	cost	start_date
1	Nikita	Jaipur	India	3	18500.00	2026-01-12
2	Omar	Dubai	UAE	4	62000.00	2026-02-02
3	Pallavi	Goa	India	5	27500.00	2026-02-15
4	Nikita	Singapore	Singapore	4	71000.00	2026-03-01
5	Quentin	Munnar	India	2	12400.00	2026-03-08
6	Riya	Dubai	UAE	3	48500.00	2026-03-20
7	Omar	Jaipur	India	2	9800.00	2026-04-04
8	Sameer	Bali	Indonesia	6	88000.00	NULL

Figure 1: the written order of SELECT clauses differs from the order MySQL processes them.

2. Selecting All Columns

query.sql

SELECT * FROM trips WHERE trip_id <= 3;

Result: 3 rows

trip_id	traveller	city	country	nights	cost	start_date
1	Nikita	Jaipur	India	3	18500.00	2026-01-12
2	Omar	Dubai	UAE	4	62000.00	2026-02-02
3	Pallavi	Goa	India	5	27500.00	2026-02-15

SELECT * is fine for exploring, but in application code list the columns you need. It sends less data and doesn't break when columns are added.

3. Selecting Specific Columns

query.sql

SELECT traveller, city, cost FROM trips;

Result: 8 rows

traveller	city	cost
Nikita	Jaipur	18500.00
Omar	Dubai	62000.00
Pallavi	Goa	27500.00
Nikita	Singapore	71000.00
Quentin	Munnar	12400.00
Riya	Dubai	48500.00
Omar	Jaipur	9800.00
Sameer	Bali	88000.00

4. Column Aliases with AS

Rename result columns for readability. Quote aliases that contain spaces:

query.sql

SELECT traveller AS name, city AS destination, cost AS `Total cost (₹)`
FROM trips
WHERE country = 'India';

Result: 4 rows

name	destination	Total cost (₹)
Nikita	Jaipur	18500.00
Pallavi	Goa	27500.00
Quentin	Munnar	12400.00
Omar	Jaipur	9800.00

5. Expressions and Functions

The select list can calculate new values:

expressions.sql

SELECT traveller,
       city,
       cost,
       ROUND(cost / nights, 2)          AS cost_per_night,
       UPPER(LEFT(country, 3))          AS country_code,
       start_date + INTERVAL nights DAY AS end_date
FROM trips
WHERE trip_id IN (1, 2, 3);

Result: 3 rows

traveller	city	cost	cost_per_night	country_code	end_date
Nikita	Jaipur	18500.00	6166.67	IND	2026-01-15
Omar	Dubai	62000.00	15500.00	UAE	2026-02-06
Pallavi	Goa	27500.00	5500.00	IND	2026-02-20

You can even SELECT without a table to evaluate expressions:

query.sql

SELECT 18 * 4 AS product, CONCAT('Trip', '-', 2026) AS label, DAYNAME('2026-03-20') AS weekday;

Result: 1 row

product	label	weekday
72	Trip-2026	Friday

6. Removing Duplicates with DISTINCT

query.sql

SELECT DISTINCT country FROM trips;

Result: 4 rows

country
India
UAE
Singapore
Indonesia

With several columns, DISTINCT applies to the whole combination:

query.sql

SELECT DISTINCT traveller, country FROM trips ORDER BY traveller;

Result: 8 rows

traveller	country
Nikita	India
Nikita	Singapore
Omar	India
Omar	UAE
Pallavi	India
Quentin	India
Riya	UAE
Sameer	Indonesia

query.sql

SELECT COUNT(DISTINCT traveller) AS travellers, COUNT(DISTINCT city) AS cities FROM trips;

Result: 1 row

travellers	cities
6	6

7. Filtering with WHERE

query.sql

SELECT traveller, city, cost FROM trips
WHERE country = 'India' AND cost < 20000;

Result: 3 rows

traveller	city	cost
Nikita	Jaipur	18500.00
Quentin	Munnar	12400.00
Omar	Jaipur	9800.00

query.sql

SELECT traveller, city FROM trips WHERE start_date IS NULL;

Result: 1 row

traveller	city
Sameer	Bali

See MySQL WHERE for every kind of condition.

8. Sorting and Limiting

query.sql

SELECT city, cost FROM trips ORDER BY cost DESC LIMIT 3;

Result: 3 rows

city	cost
Bali	88000.00
Singapore	71000.00
Dubai	62000.00

query.sql

SELECT traveller, city, start_date FROM trips
ORDER BY start_date DESC
LIMIT 2 OFFSET 1;   -- skip the newest, take the next 2

Result: 2 rows

traveller	city	start_date
Riya	Dubai	2026-03-20
Quentin	Munnar	2026-03-08

9. Conditional Values with CASE

case.sql

SELECT city, cost,
       CASE
         WHEN cost >= 60000 THEN 'Luxury'
         WHEN cost >= 20000 THEN 'Standard'
         ELSE 'Budget'
       END AS trip_class
FROM trips
ORDER BY cost DESC;

Result: 8 rows

city	cost	trip_class
Bali	88000.00	Luxury
Singapore	71000.00	Luxury
Dubai	62000.00	Luxury
Dubai	48500.00	Standard
Goa	27500.00	Standard
Jaipur	18500.00	Budget
Munnar	12400.00	Budget
Jaipur	9800.00	Budget

10. Summaries with GROUP BY

group.sql

SELECT country, COUNT(*) AS trips, SUM(nights) AS nights, ROUND(AVG(cost)) AS avg_cost
FROM trips
GROUP BY country
HAVING COUNT(*) > 1
ORDER BY trips DESC;

Result: 2 rows

country	trips	nights	avg_cost
India	4	12	17050
UAE	2	7	55250

11. Sub-queries

A query inside a query. Trips that cost more than the average:

query.sql

SELECT traveller, city, cost
FROM trips
WHERE cost > (SELECT AVG(cost) FROM trips)
ORDER BY cost;

Result: 4 rows

traveller	city	cost
Riya	Dubai	48500.00
Omar	Dubai	62000.00
Nikita	Singapore	71000.00
Sameer	Bali	88000.00

A sub-query in the select list, giving each trip's share of its traveller's total spend:

correlated.sql

SELECT traveller, city, cost,
       ROUND(100 * cost / (SELECT SUM(cost) FROM trips t2 WHERE t2.traveller = t.traveller), 1) AS pct_of_traveller_total
FROM trips t
WHERE traveller IN ('Nikita', 'Omar')
ORDER BY traveller, cost DESC;

Result: 4 rows

traveller	city	cost	pct_of_traveller_total
Nikita	Singapore	71000.00	79.3
Nikita	Jaipur	18500.00	20.7
Omar	Dubai	62000.00	86.4
Omar	Jaipur	9800.00	13.6

12. Why an Alias Can't Be Used in WHERE

Because WHERE runs before SELECT (Figure 1), an alias defined in SELECT doesn't exist yet in WHERE. It does exist in ORDER BY:

query.sql

SELECT city, cost / nights AS per_night FROM trips WHERE per_night > 15000;

Output

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

query.sql

SELECT city, ROUND(cost / nights) AS per_night FROM trips WHERE cost / nights > 15000 ORDER BY per_night DESC;

Result: 3 rows

city	per_night
Singapore	17750
Dubai	16167
Dubai	15500

13. SELECT into Variables

variables.sql

SELECT MAX(cost), MIN(cost) INTO @max_cost, @min_cost FROM trips;
SELECT @max_cost AS most_expensive, @min_cost AS cheapest, @max_cost - @min_cost AS spread;

Result: 1 row

most_expensive	cheapest	spread
88000.00	9800.00	78200.000000000000000000000000000000

14. SELECT from Java

TripReport.java

String sql = "SELECT traveller, city, cost FROM trips WHERE country = ? ORDER BY cost DESC";
try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setString(1, "India");
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.printf("%-8s %-8s %10s%n",
                rs.getString("traveller"), rs.getString("city"), rs.getBigDecimal("cost"));
        }
    }
}

15. Practice Exercises

Exercise 1: List the cities visited in India, without duplicates, alphabetically.

solution.sql

SELECT DISTINCT city FROM trips WHERE country = 'India' ORDER BY city;

Result: 3 rows

city
Goa
Jaipur
Munnar

Exercise 2: Show each traveller's total spend and number of trips, biggest spender first.

solution.sql

SELECT traveller, COUNT(*) AS trips, SUM(cost) AS total_spend
FROM trips
GROUP BY traveller
ORDER BY total_spend DESC;

Result: 6 rows

traveller	trips	total_spend
Nikita	2	89500.00
Sameer	1	88000.00
Omar	2	71800.00
Riya	1	48500.00
Pallavi	1	27500.00
Quentin	1	12400.00

Exercise 3: Show the cheapest trip per night (city and cost per night).

solution.sql

SELECT city, ROUND(cost / nights, 2) AS per_night
FROM trips
ORDER BY per_night
LIMIT 1;

Result: 1 row

city	per_night
Jaipur	4900.00

16. Summary

SELECT columns FROM table reads data; list columns instead of * in applications.
Use aliases, expressions, functions and CASE to shape the output.
DISTINCT removes duplicate rows; WHERE filters; ORDER BY + LIMIT sort and cut.
GROUP BY/HAVING summarise; sub-queries nest one query inside another.
Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Was this tutorial helpful?

Help Us Improve

MySQL SELECT Statement

1. Sample Data

2. Selecting All Columns

3. Selecting Specific Columns

4. Column Aliases with AS

5. Expressions and Functions

6. Removing Duplicates with DISTINCT

7. Filtering with WHERE

8. Sorting and Limiting

9. Conditional Values with CASE

10. Summaries with GROUP BY

11. Sub-queries

12. Why an Alias Can't Be Used in WHERE

13. SELECT into Variables

14. SELECT from Java

15. Practice Exercises

16. Summary