MySQL SELECT Query Data

SELECT Basics

Treat result order as undefined without ORDER BY, and inspect an execution plan when filtering or sorting becomes expensive.

The SELECT statement retrieves data from one or more tables. It's the most frequently used SQL statement. You can select all columns with *, or list specific columns to reduce data transfer and improve clarity.

SELECT, Column Aliases, and DISTINCT

-- Select all columns (use sparingly in production)
SELECT * FROM customers;

-- Select specific columns
SELECT first_name, last_name, email FROM customers;

-- Column aliases with AS (rename output columns)
SELECT
    first_name                          AS first,
    last_name                           AS last,
    email                               AS email_address,
    CONCAT(first_name, ' ', last_name)  AS full_name
FROM customers;

-- DISTINCT: return only unique values
SELECT DISTINCT category FROM products;

-- DISTINCT on multiple columns
SELECT DISTINCT status, category FROM products;

LIMIT and OFFSET

LIMIT restricts the number of rows returned. OFFSET skips a number of rows before starting to return results - essential for pagination.

LIMIT, OFFSET, and Pagination

-- Return only the first 5 rows
SELECT * FROM products LIMIT 5;

-- Skip 10 rows, then return the next 5 (page 3 with 5 per page)
SELECT * FROM products LIMIT 5 OFFSET 10;

-- Shorthand: LIMIT offset, count
SELECT * FROM products LIMIT 10, 5;

-- Pagination formula: LIMIT page_size OFFSET (page_number - 1) * page_size
-- Page 1 (rows 1-10):
SELECT product_id, name, price FROM products ORDER BY product_id LIMIT 10 OFFSET 0;
-- Page 2 (rows 11-20):
SELECT product_id, name, price FROM products ORDER BY product_id LIMIT 10 OFFSET 10;
-- Page 3 (rows 21-30):
SELECT product_id, name, price FROM products ORDER BY product_id LIMIT 10 OFFSET 20;

SELECT with Expressions and Functions

SELECT can compute expressions, call built-in functions, and handle NULL values inline - without needing to store the computed result.

Expressions, CONCAT, IFNULL, COALESCE

-- Arithmetic expressions
SELECT
    name,
    price,
    price * 1.1                          AS price_with_tax,
    price * 0.9                          AS discounted_price,
    stock * price                        AS inventory_value
FROM products;

-- CONCAT: combine strings
SELECT
    CONCAT(first_name, ' ', last_name)   AS full_name,
    CONCAT('+1-', phone)                 AS formatted_phone
FROM customers;

-- IFNULL: replace NULL with a default value
SELECT
    name,
    IFNULL(description, 'No description available') AS description
FROM products;

-- COALESCE: return the first non-NULL value from a list
SELECT
    customer_id,
    COALESCE(phone, email, 'No contact info') AS contact
FROM customers;

-- SELECT without a table (compute expressions directly)
SELECT 2 + 2 AS result;
SELECT NOW() AS current_time;
SELECT VERSION() AS mysql_version;

Reading exactly the rows and columns you need with SELECT

SELECT is the most used MySQL statement because it reads data from tables. A careful SELECT names the columns needed, chooses the table, filters rows with WHERE, sorts with ORDER BY, and limits large result sets when appropriate. Selecting everything with * may be fine while exploring, but production queries should be intentional.

The quality of a SELECT query depends on both correctness and cost. A query that returns the right rows but scans a huge table unnecessarily may become slow. Conditions, indexes, joins, aliases, and limit clauses all affect how useful the query is in real applications.

Select specific columns instead of using * everywhere.
Use WHERE to narrow rows.
Use ORDER BY only when result order matters.
Use LIMIT for previews and paginated screens.

Select active users for a recent list

SELECT id, name, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20;

Before you move on