MySQL SELECT Queries

What is the SELECT Statement?

The SELECT statement in MySQL is used to retrieve data from a database table. It allows you to extract all columns, specific columns, or rows that meet certain conditions. This makes SELECT a powerful tool for reporting, analytics, and application development.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name;

• column1, column2, ... – Columns to retrieve.
• table_name – The table from which data is selected.

To select all columns in a table, you can use the asterisk (*):

SELECT * FROM table_name;

Simple SELECT Queries

Let’s assume we have an employees table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10,2)
);

Example 1: Retrieve All Columns

SELECT * FROM employees;

Step-by-Step Analysis:

• The * selects all columns in the employees table.
• This query is useful for quickly viewing the entire dataset.

Example 2: Retrieve Specific Columns

SELECT first_name, last_name, email FROM employees;

Step-by-Step Analysis:

• Only first_name, last_name, and email are retrieved.
• Selecting specific columns reduces unnecessary data transfer and improves query performance.

Conditional SELECT Queries

Conditional queries allow you to filter rows based on specific criteria using the WHERE clause.

Basic Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example 1: Filter by Salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

Logic Behind the Query:

• WHERE salary > 50000 retrieves only employees with a salary greater than 50,000.
• The condition ensures that only relevant rows are returned, making data analysis more targeted.

Example 2: Combine Multiple Conditions

SELECT first_name, last_name, hire_date
FROM employees
WHERE salary > 50000 AND hire_date >= '2025-01-01';

Logic Behind the Query:

• AND combines two conditions, requiring both to be true.
• You can also use OR to include rows that meet either condition.

Example 3: Pattern Matching with LIKE

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@example.com';

Logic Behind the Query:

• % is a wildcard representing any sequence of characters.
• This query retrieves all employees whose email ends with @example.com.

Ordering and Limiting Results

Example 1: Sorting Results

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Step-by-Step Analysis:

• ORDER BY salary DESC sorts the results by salary in descending order.
• ASC can be used for ascending order.

Example 2: Limiting Results

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Step-by-Step Analysis:

• LIMIT 5 retrieves only the top 5 highest-paid employees.
• Limiting results is essential for dashboards, reports, or performance optimization.

Key Principles for Writing Efficient SELECT Queries

1. Select Only What You Need: Avoid SELECT * for large tables; choose specific columns.
2. Filter Early: Use WHERE to reduce the number of rows processed.
3. Combine Conditions Thoughtfully: Use AND, OR, and parentheses to define complex filters clearly.
4. Use Pattern Matching Carefully: LIKE is flexible but can be slow on large datasets; use indexes if possible.
5. Sort and Limit Strategically: Apply ORDER BY and LIMIT to control and optimize query results.