MySQL SELECT Query Data: Tutorial, Examples, FAQs & Interview Tips

SELECT Basics

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 tl-table (compute expressions directly)
SELECT 2 + 2 AS result;
SELECT NOW() AS current_time;
SELECT VERSION() AS mysql_version;

Previous Next

Key Takeaways

SELECT * is convenient but slow - always specify only the columns you need.
Use column aliases with AS for readable output: SELECT first_name AS name.
DISTINCT removes duplicate rows from results - use it only when needed as it adds overhead.
LIMIT restricts the number of rows returned - always use with ORDER BY for consistent pagination.
CONCAT() combines strings; IFNULL(col, default) handles NULL values gracefully.
Calculated columns in SELECT can use arithmetic: SELECT price * quantity AS total.

Common Mistakes to Avoid

WRONG SELECT * FROM orders

RIGHT SELECT id, customer_id, total FROM orders

SELECT * fetches all columns including unused ones - wastes bandwidth and prevents index-only scans.

WRONG SELECT DISTINCT * FROM large_table

RIGHT Add proper WHERE conditions first

DISTINCT requires sorting all results to find duplicates - very slow on large tables. Fix the root cause of duplicates instead.

Frequently Asked Questions

What is the order of execution in a SQL SELECT statement?

SQL executes in this order: FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT. Understanding this helps write correct queries and avoid referencing aliases in WHERE clauses.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping - it cannot use aggregate functions. HAVING filters groups after GROUP BY - it can use aggregate functions like COUNT(), SUM(), AVG().

How do I select the top N rows in MySQL?

Use LIMIT: SELECT * FROM products ORDER BY price DESC LIMIT 10. Always include ORDER BY with LIMIT for consistent results - without it, the order is undefined.

MySQL SELECT Query Data: Tutorial, Examples, FAQs & Interview Tips

SELECT Basics

LIMIT and OFFSET

SELECT with Expressions and Functions

Level Up Your My sql Skills

Frequently Asked Questions

Popular Tutorials

Ready to Level Up Your Skills?