MySQL SELECT Query

Basic SELECT Syntax

The SELECT statement is used to query data from MySQL database tables.

Simple SELECT

-- Select all columns from a table
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Select with column aliases
SELECT 
    id AS user_id,
    name AS user_name,
    email AS user_email
FROM users;

-- Select with expressions
SELECT 
    id,
    name,
    LENGTH(name) AS name_length,
    UPPER(name) AS name_upper
FROM users;

-- Select with calculations
SELECT 
    price,
    quantity,
    price * quantity AS total_value
FROM products;

WHERE Clause Filtering

-- Equality condition
SELECT * FROM users WHERE status = 'active';

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price >= 50 AND price <= 100;
SELECT * FROM products WHERE price <> 99.99;

-- IN operator
SELECT * FROM users WHERE country IN ('USA', 'UK', 'Canada');

-- BETWEEN operator
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- LIKE operator for pattern matching
SELECT * FROM users WHERE name LIKE 'J%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- IS NULL
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- Combining conditions
SELECT * FROM users 
WHERE status = 'active' 
    AND country = 'USA'
    AND created_at > '2023-01-01';

ORDER BY

-- Ascending order (default)
SELECT * FROM users ORDER BY name;

-- Explicit ascending
SELECT * FROM users ORDER BY name ASC;

-- Descending order
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple column sorting
SELECT * FROM users 
ORDER BY country ASC, name ASC;

-- Sorting by position
SELECT id, name, email FROM users ORDER BY 2;

-- Sorting with expressions
SELECT * FROM users ORDER BY LENGTH(name);

-- NULL handling in sorting
SELECT * FROM users ORDER BY deleted_at NULLS LAST;

LIMIT and OFFSET

-- Limit number of results
SELECT * FROM users LIMIT 10;

-- Skip first 10 results
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Alternative syntax
SELECT * FROM users LIMIT 20, 10;

-- Pagination example (page 3 with 10 items per page)
SELECT * FROM users LIMIT 20, 10;

-- Get latest records
SELECT * FROM users ORDER BY created_at DESC LIMIT 5;

-- Random sampling
SELECT * FROM users ORDER BY RAND() LIMIT 10;

DISTINCT

-- Get unique values
SELECT DISTINCT country FROM users;

-- Multiple columns
SELECT DISTINCT country, status FROM users;

-- With WHERE clause
SELECT DISTINCT status FROM users WHERE country = 'USA';

-- Counting distinct values
SELECT COUNT(DISTINCT country) AS country_count FROM users;

Aggregation Functions

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count specific column (non-NULL values)
SELECT COUNT(email) FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT country) FROM users;

-- Sum values
SELECT SUM(price) FROM products;
SELECT SUM(price) AS total_price FROM products WHERE category = 'electronics';

-- Average value
SELECT AVG(price) FROM products;

-- Minimum value
SELECT MIN(price) FROM products;

-- Maximum value
SELECT MAX(price) FROM products;

-- Multiple aggregates
SELECT 
    COUNT(*) AS total_users,
    COUNT(DISTINCT country) AS countries,
    AVG(age) AS avg_age,
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM users;

GROUP BY

-- Group by single column
SELECT status, COUNT(*) FROM users GROUP BY status;

-- Group by multiple columns
SELECT 
    country, 
    status, 
    COUNT(*) AS user_count
FROM users 
GROUP BY country, status;

-- With HAVING clause
SELECT 
    country, 
    COUNT(*) AS user_count
FROM users 
GROUP BY country 
HAVING COUNT(*) > 100;

-- With WHERE and ORDER BY
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE is_active = 1
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

Subqueries

-- Simple subquery
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- Correlated subquery
SELECT u.*,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- EXISTS subquery
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.created_at > '2023-01-01'
);

-- FROM subquery
SELECT * FROM (
    SELECT country, COUNT(*) AS user_count
    FROM users
    GROUP BY country
) AS country_stats
WHERE user_count > 50;

JOIN Operations

-- INNER JOIN
SELECT u.name, o.order_number, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.order_number, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.order_number, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Self JOIN
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Multiple JOINs
SELECT 
    u.name,
    o.order_number,
    p.name AS product_name,
    oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

UNION

-- Combine results from multiple queries
SELECT name, email FROM active_users
UNION
SELECT name, email FROM pending_users;

-- UNION ALL (includes duplicates)
SELECT user_id FROM orders_2022
UNION ALL
SELECT user_id FROM orders_2023;

-- With ORDER BY
SELECT id, name, 'customer' AS type FROM customers
UNION ALL
SELECT id, name, 'supplier' AS type FROM suppliers
ORDER BY name;

Conditional Expressions

-- CASE expression
SELECT 
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- Simple CASE
SELECT 
    name,
    status,
    CASE status
        WHEN 'active' THEN 'User is active'
        WHEN 'pending' THEN 'Awaiting approval'
        ELSE 'Unknown status'
    END AS status_description
FROM users;

-- IF function
SELECT 
    name,
    IF(age >= 18, 'Adult', 'Minor') AS age_group
FROM users;

-- IFNULL
SELECT 
    name,
    IFNULL(phone, 'Not provided') AS phone
FROM users;

-- COALESCE
SELECT 
    name,
    COALESCE(mobile_phone, home_phone, work_phone, 'No phone') AS phone
FROM users;

Date and Time Functions

-- Current date/time
SELECT CURDATE(), CURTIME(), NOW(), CURRENT_TIMESTAMP;

-- Date extraction
SELECT 
    created_at,
    YEAR(created_at) AS year,
    MONTH(created_at) AS month,
    DAY(created_at) AS day,
    HOUR(created_at) AS hour,
    MINUTE(created_at) AS minute,
    SECOND(created_at) AS second,
    DAYNAME(created_at) AS day_name,
    MONTHNAME(created_at) AS month_name,
    QUARTER(created_at) AS quarter,
    WEEK(created_at) AS week,
    DAYOFYEAR(created_at) AS day_of_year,
    DAYOFWEEK(created_at) AS day_of_week;

-- Date arithmetic
SELECT 
    created_at,
    DATE_ADD(created_at, INTERVAL 1 DAY) AS tomorrow,
    DATE_SUB(created_at, INTERVAL 7 DAY) AS last_week,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS formatted;

-- Date difference
SELECT 
    DATEDIFF('2024-01-15', '2024-01-01') AS days_diff,
    TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_ago
FROM users;

String Functions

-- String manipulation
SELECT 
    name,
    LENGTH(name) AS length,
    UPPER(name) AS uppercase,
    LOWER(name) AS lowercase,
    LEFT(name, 3) AS first_three,
    RIGHT(name, 3) AS last_three,
    SUBSTRING(name, 2, 3) AS substring,
    TRIM(name) AS trimmed,
    REPLACE(name, ' ', '-') AS replaced,
    CONCAT(name, ' (', email, ')') AS full_info,
    CONCAT_WS(' - ', name, email, phone) AS concat_ws
FROM users;

Pattern Matching

-- LIKE patterns
SELECT * FROM users WHERE name LIKE 'A%';          -- Starts with A
SELECT * FROM users WHERE name LIKE '%a%';         -- Contains a
SELECT * FROM users WHERE name LIKE '%n';          -- Ends with n
SELECT * FROM users WHERE name LIKE '_a%';         -- Second letter is a
SELECT * FROM users WHERE name LIKE 'J__n%';       -- J followed by 2 chars, then n

-- REGEXP (regular expressions)
SELECT * FROM users WHERE name REGEXP '^J';        -- Starts with J
SELECT * FROM users WHERE name REGEXP 'n$';        -- Ends with n
SELECT * FROM users WHERE name REGEXP '[aeiou]';  -- Contains vowel
SELECT * FROM users WHERE name REGEXP '^[A-Z]';   -- Starts with uppercase
SELECT * FROM users WHERE email REGEXP '^[a-z]+\.[a-z]+@';

WITH (Common Table Expressions)

-- Non-recursive CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE created_at > '2023-01-01';

-- Multiple CTEs
WITH 
    high_value_orders AS (
        SELECT * FROM orders WHERE total > 1000
    ),
    customer_stats AS (
        SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
        FROM high_value_orders
        GROUP BY user_id
    )
SELECT * FROM customer_stats WHERE order_count > 5;

LIMIT Variations

-- Top N by value
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- Top N with offset (pagination)
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

-- Random sample
SELECT * FROM users ORDER BY RAND() LIMIT 100;

-- First and last
SELECT * FROM users ORDER BY id LIMIT 1;              -- First
SELECT * FROM users ORDER BY id DESC LIMIT 1;         -- Last

-- With GROUP BY
SELECT status, COUNT(*) as count 
FROM users 
GROUP BY status 
ORDER BY count DESC 
LIMIT 3;

Comprehensive Example

SELECT 
    -- Column selection with aliases
    u.id AS user_id,
    u.name AS user_name,
    u.email,
    u.country,
    u.created_at AS joined_date,
    
    -- Aggregates
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total), 0) AS total_spent,
    AVG(o.total) AS avg_order_value,
    
    -- Conditional expressions
    CASE 
        WHEN COUNT(o.id) = 0 THEN 'No orders'
        WHEN COUNT(o.id) < 5 THEN 'New customer'
        WHEN COUNT(o.id) < 20 THEN 'Regular customer'
        ELSE 'VIP customer'
    END AS customer_tier,
    
    -- Date functions
    DATEDIFF(CURDATE(), MAX(o.created_at)) AS days_since_last_order,
    
    -- String functions
    CONCAT(UPPER(LEFT(u.country, 2)), '-', u.id) AS customer_code
    
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
    AND u.created_at >= '2022-01-01'
GROUP BY u.id, u.name, u.email, u.country, u.created_at, u.status
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC
LIMIT 100;

Summary

The SELECT statement is the foundation of data retrieval in MySQL:

• Basic Queries: Use SELECT with specific columns and conditions
• Filtering: WHERE clause for precise data selection
• Sorting: ORDER BY for result ordering
• Limiting: LIMIT and OFFSET for pagination
• Aggregation: COUNT, SUM, AVG, MIN, MAX
• Grouping: GROUP BY with HAVING
• Joins: INNER, LEFT, RIGHT, SELF joins
• Subqueries: Nested queries for complex logic
• Expressions: CASE, IF, COALESCE for conditional logic
• CTEs: WITH clause for reusable subqueries

---

Previous: Insert Data

Next: WHERE Clause