MySql - Retrieving data

Retrieving Data Using the SELECT Statement in MySQL

Retrieving data from a database is one of the most fundamental operations in MySQL. This is achieved through the SELECT statement, which is used to query one or more tables in order to fetch specific records. The power of SELECT lies in its versatility—allowing filtering, sorting, aggregation, and joining of data. This document provides comprehensive notes on the SELECT statement, covering everything from basic usage to complex queries, all in over 2000 words.

1. Introduction to SELECT

1.1 What is SELECT?

The SELECT statement is used to retrieve rows from one or more tables or views in a MySQL database. The basic syntax is:

SELECT column1, column2, ...
FROM table_name;

1.2 Why SELECT is Important

The SELECT statement allows users and applications to access stored information. Without it, data manipulation would be limited to insertion, deletion, or updating without any retrieval capabilities. It's essential for reports, dashboards, and business logic.

2. Basic SELECT Queries

2.1 Selecting All Columns

Use the asterisk (*) to select all columns from a table:

SELECT * FROM employees;

This will return every column for every row in the employees  table.

2.2 Selecting Specific Columns

You can specify which columns to retrieve:

SELECT first_name, last_name FROM employees;

This is faster and more efficient, especially for large tables with many columns.

2.3 Using Aliases

You can rename columns in the result set using AS:

SELECT first_name AS 'First Name', last_name AS 'Last Name' FROM employees;

3. Filtering Rows with WHERE

3.1 Basic WHERE Clause

The WHERE clause filters rows based on conditions:

SELECT * FROM employees WHERE department = 'Sales';

3.2 Comparison Operators

• = : Equal to
• <> or != : Not equal to
• < : Less than
• > : Greater than
• <= : Less than or equal to
• >= : Greater than or equal to

3.3 Logical Operators

• AND – both conditions must be true
• OR – at least one condition must be true
• NOT – negates a condition

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;

4. Pattern Matching with LIKE

4.1 Basic LIKE Syntax

SELECT * FROM employees WHERE first_name LIKE 'J%';

This finds all names starting with "J".

4.2 Wildcards

• %: matches any number of characters
• _: matches exactly one character

SELECT * FROM employees WHERE last_name LIKE '_mith';

5. Sorting Data Using ORDER BY

5.1 Basic ORDER BY

SELECT * FROM employees ORDER BY last_name;

5.2 Sorting in Descending Order

SELECT * FROM employees ORDER BY salary DESC;

5.3 Sorting by Multiple Columns

SELECT * FROM employees ORDER BY department ASC, salary DESC;

6. Limiting Results with LIMIT

6.1 Basic LIMIT Usage

SELECT * FROM employees LIMIT 10;

6.2 LIMIT with OFFSET

SELECT * FROM employees LIMIT 5 OFFSET 10;

This skips the first 10 rows and returns the next 5.

7. Aggregate Functions

7.1 COUNT

SELECT COUNT(*) FROM employees;

7.2 SUM

SELECT SUM(salary) FROM employees;

7.3 AVG

SELECT AVG(salary) FROM employees;

7.4 MAX and MIN

SELECT MAX(salary), MIN(salary) FROM employees;

8. GROUP BY and HAVING

8.1 Using GROUP BY

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

8.2 Using HAVING to Filter Aggregates

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

9. Joining Tables

9.1 INNER JOIN

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

9.2 LEFT JOIN

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

9.3 RIGHT JOIN

SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

9.4 FULL JOIN (Not Directly Supported)

MySQL does not support FULL OUTER JOIN directly, but it can be emulated using UNION:

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

10. Subqueries

10.1 Subquery in SELECT

SELECT first_name, (SELECT MAX(salary) FROM employees) AS max_salary
FROM employees;

10.2 Subquery in WHERE

SELECT first_name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

11. DISTINCT and Eliminating Duplicates

11.1 Using DISTINCT

SELECT DISTINCT department FROM employees;

11.2 DISTINCT with Multiple Columns

SELECT DISTINCT department, job_title FROM employees;

12. CASE Statements

12.1 Basic CASE Usage

SELECT first_name, salary,
CASE 
  WHEN salary > 70000 THEN 'High'
  WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
  ELSE 'Low'
END AS salary_range
FROM employees;

13. Working with NULL Values

13.1 IS NULL / IS NOT NULL

SELECT * FROM employees WHERE last_login IS NULL;

13.2 IFNULL and COALESCE

SELECT first_name, IFNULL(last_login, 'Never') FROM employees;

SELECT first_name, COALESCE(phone, 'N/A') FROM employees;

14. Views and SELECT

14.1 Creating Views

CREATE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'active';

14.2 Selecting from Views

SELECT * FROM active_employees;

The SELECT statement in MySQL is a powerful tool for data retrieval. With the wide range of clauses, functions, and features available, it can serve everything from simple queries to complex reporting needs. Understanding how to effectively use SELECT will enhance your ability to interact with MySQL databases efficiently and accurately. Mastery of SELECT empowers you to write optimized queries, uncover insights, and build robust applications that depend on dynamic data access.From basic usage to filtering, sorting, grouping, and joining, the SELECT statement forms the backbone of any data-driven application. Investing time to fully understand and utilize its features will lead to more maintainable and performant database systems.