SELECT Query

SELECT is the DQL statement used to retrieve data from a database. TiDB supports most of MySQL's SELECT features.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column [ASC|DESC]
LIMIT count OFFSET offset;

Basic Examples

Select All Columns

SELECT * FROM users;

Select Specific Columns

SELECT name, email FROM users;

Where Clause

-- Filter with a condition
SELECT * FROM users WHERE status = 'active';

-- Multiple conditions
SELECT * FROM users
WHERE status = 'active' AND created_at > '2024-01-01';

-- IN operator
SELECT * FROM users WHERE status IN ('active', 'pending');

-- BETWEEN
SELECT * FROM orders WHERE total BETWEEN 100000 AND 500000;

-- LIKE
SELECT * FROM users WHERE name LIKE '%John%';

ORDER BY

-- Sort ascending (default)
SELECT * FROM products ORDER BY price ASC;

-- Sort descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM orders ORDER BY created_at DESC, total DESC;

LIMIT and OFFSET

-- Take the first 10 rows
SELECT * FROM products LIMIT 10;

-- Pagination (page 3, 10 items per page)
SELECT * FROM products LIMIT 10 OFFSET 20;

Aggregate Functions

-- Count the number of rows
SELECT COUNT(*) FROM users;

-- Count rows with a condition
SELECT COUNT(*) AS active_users FROM users WHERE status = 'active';

-- SUM, AVG, MIN, MAX
SELECT
    category_id,
    COUNT(*) AS total_products,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category_id;

GROUP BY

-- Grouping data
SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status;

-- With HAVING
SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status
HAVING count > 10;

Subquery

-- Subquery in WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000000);

-- Subquery in FROM
SELECT avg_total.*
FROM (SELECT user_id, AVG(total) AS avg_total FROM orders GROUP BY user_id) avg_total
WHERE avg_total.avg_total > 500000;

Common Table Expression (CTE)

WITH active_orders AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT u.name, a.total_spent
FROM users u
JOIN active_orders a ON u.id = a.user_id
WHERE a.total_spent > 1000000;

EXPLAIN

To view the execution plan:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN ANALYZE SELECT COUNT(*) FROM orders GROUP BY status;

In the next chapter, we will look at JOINs in TiDB.