2. Mastering MySQL SELECT Queries: A Complete Guide with Examples

📌 Description

Understanding how to retrieve data efficiently from a database is a fundamental skill for any developer or data analyst. In this comprehensive guide, we explore the power and versatility of MySQL SELECT queries, the cornerstone of any SQL-based data interaction. Whether you’re managing a small project or a large-scale application, mastering the SELECT statement helps you extract meaningful insights from your data.

This blog will take you from the basics—like creating a database and table—to more advanced SELECT operations, including filtering with WHERE, sorting with ORDER BY, and using aggregate functions like COUNT and AVG. You’ll also learn how to select specific fields, rename columns with aliases, use conditions like BETWEEN and IN, and join multiple tables effectively.

We’ll demonstrate every step with real SQL code examples, so you can follow along and experiment on your local MySQL server. Whether you’re a beginner or looking to brush up your knowledge, this tutorial gives you a solid foundation in crafting precise and efficient SELECT queries in MySQL. Let’s dive into the world of querying with SQL and unlock the full potential of your data.

🧠 MySQL SELECT Queries – Complete Guide

🛠️ Step 1: Creating a MySQL Database

Before querying data, we need a database. Here’s how to create one in MySQL:

CREATE DATABASE company_db;

To use this database:

USE company_db;

🧱 Step 2: Creating a Table

Next, let’s create an employees table with some basic fields.

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

Now insert sample data:

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('John', 'Doe', 'Engineering', 60000, '2020-01-15'),
('Jane', 'Smith', 'Marketing', 50000, '2021-03-10'),
('Alice', 'Johnson', 'Engineering', 70000, '2019-07-20'),
('Bob', 'Brown', 'HR', 45000, '2022-09-01');

🔍 Step 3: Basic SELECT Query

The simplest SELECT query retrieves all rows and all columns:

SELECT * FROM employees;

This will return the entire table.

🎯 Selecting Specific Columns

You can choose to view only selected fields:

SELECT first_name, last_name FROM employees;

📝 Using Column Aliases

You can rename columns in your result using AS:

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

🎛️ Filtering Records with WHERE Clause

You can filter results based on conditions:

SELECT * FROM employees WHERE department = 'Engineering';

You can also use comparison operators:

SELECT * FROM employees WHERE salary > 50000;

🔁 Using Logical Operators

Use AND, OR, and NOT for complex conditions:

SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 60000;

🧮 Using Aggregate Functions

Count employees:

SELECT COUNT(*) FROM employees;

Get average salary:

SELECT AVG(salary) AS avg_salary FROM employees;

Find the highest salary:

SELECT MAX(salary) FROM employees;

📊 GROUP BY and HAVING

Group data by department:

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

Filter grouped results using HAVING:

SELECT department, COUNT(*) AS total 
FROM employees 
GROUP BY department 
HAVING total > 1;

🪜 ORDER BY Clause

Sort employees by salary:

SELECT * FROM employees 
ORDER BY salary DESC;

🎯 IN, BETWEEN, LIKE

Use IN to filter by multiple values:

SELECT * FROM employees 
WHERE department IN ('Engineering', 'HR');

Use BETWEEN for ranges:

SELECT * FROM employees 
WHERE salary BETWEEN 45000 AND 65000;

Use LIKE for pattern matching:

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

🧩 Using SELECT with JOINs

Create a second table departments:

CREATE TABLE departments (
    department_name VARCHAR(50) PRIMARY KEY,
    manager VARCHAR(50)
);

INSERT INTO departments (department_name, manager) VALUES
('Engineering', 'Tom Harris'),
('Marketing', 'Emma Watson'),
('HR', 'Rachel Green');

Now join employees with departments:

SELECT e.first_name, e.last_name, d.manager
FROM employees e
JOIN departments d ON e.department = d.department_name;

🎯 DISTINCT Keyword

To get unique department names:

SELECT DISTINCT department FROM employees;

📐 Limiting Results with LIMIT

To get only the top 3 earners:

SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 3;

✅ Using IFNULL and COALESCE

Handle null values:

SELECT first_name, IFNULL(salary, 0) FROM employees;

🧪 Subqueries

Get employees with salary above average:

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

🧾 Summary

MySQL SELECT queries are essential for retrieving data from your databases. This guide walked you through creating a database and table, inserting sample data, and using SELECT statements with different clauses and conditions. You learned how to filter, sort, group, and join data, as well as use functions like COUNT, AVG, and MAX. Mastering these techniques helps you retrieve the right information quickly and accurately. Whether you’re working with one table or multiple joined tables, SELECT empowers you to extract valuable insights from your data. Practice regularly, and you’ll become proficient at writing complex queries with ease.