MySQL SELECT Statement Guide

In this guide, we present a detailed explanation of the MySQL Select and MySQL DISTINCT statements and provide concrete examples and applications.

MySQL SELECT statement

In MySQL, the SELECT statement is pivotal, serving to retrieve data from one or more tables in the database. For beginners trying to navigate through MySQL, understanding the SELECT statement is crucial as it forms the basis for extracting useful information from the database.

Basic Usage of MySQL Select Statement

Syntax

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

Example

Imagine we have a table named Employees with the following columns: ID, Name, Position, and Salary.

To select all employees’ names from this table:

SELECT Name
FROM Employees;

This will retrieve a list of all names from the Employees table.

Adding Conditions with WHERE

Syntax

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

Example

To select names of employees who are Managers:

SELECT Name
FROM Employees
WHERE Position = 'Manager';

Using ORDER BY to Sort Results

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column [ASC|DESC];

Example

To select all names from Employees, ordered by Salary in descending order:

SELECT Name
FROM Employees
ORDER BY Salary DESC;

Applying Aggregation Functions

You can use aggregate functions like SUM(), AVG(), MIN(), and MAX() to perform operations on data.

Example

To find the highest salary in the Employees table:

SELECT MAX(Salary)
FROM Employees;

Joining Tables

When data is spread across multiple tables, you can use JOIN clauses to retrieve it in a coherent way.

Syntax

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column = table2.column
WHERE condition;

Example

Imagine a second table named Departments with columns: ID, EmployeeID, and DepartmentName.

To select all employees' names and their respective departments:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
JOIN Departments ON Employees.ID = Departments.EmployeeID;

Grouping Data with GROUP BY

Syntax

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
WHERE condition
GROUP BY column1;

Example

To find the average salary for each position:

SELECT Position, AVG(Salary)
FROM Employees
GROUP BY Position;

Filtering Grouped Results with HAVING

Syntax

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING condition2;

Example

To find the positions with an average salary greater than 50000:

SELECT Position, AVG(Salary)
FROM Employees
GROUP BY Position
HAVING AVG

SELECT DISTINCT Statement in MySQL

The SELECT DISTINCT statement in MySQL is used to return distinct (different) values from a database table, filtering out any duplicate entries. This is particularly useful when you want to know the different kinds of data available in a column without repetition.

Basic Syntax

SELECT DISTINCT column_name
FROM table_name
WHERE condition;

Here:

• SELECT DISTINCT: This combination of keywords tells MySQL to return unique non-duplicate values.
• column_name: The name of the column from which you want to retrieve values.
• table_name: The name of the table from which to retrieve data.
• WHERE: An optional clause to filter the results.

Example 1: Basic Usage

Imagine you have a table named Products that includes the following data:

To retrieve all distinct items from the Category column:

SELECT DISTINCT Category
FROM Products;

Output:

| Category  |
|-----------|
| Fruit     |
| Vegetable |

Example 2: Combining With WHERE Clause

The WHERE clause can be added to SELECT DISTINCT to filter the results based on a particular condition.

For example, to select distinct categories where the name is not 'Apple':

SELECT DISTINCT Category
FROM Products
WHERE Name != 'Apple';

Output:

| Category  |
|-----------|
| Fruit     |
| Vegetable |

Example 3: Multiple Columns

You can also use SELECT DISTINCT with multiple columns, returning unique combinations of the columns.

Suppose you add a new column Color to the Products table:

To retrieve all distinct combinations of Category and Color:

SELECT DISTINCT Category, Color
FROM Products;

Output:

| Category  | Color  |
|-----------|--------|
| Fruit     | Red    |
| Fruit     | Yellow |
| Vegetable | Orange |
| Fruit     | Green  |
| Fruit     | Orange |

Example 4: Counting Distinct Values

You can count the number of distinct values in a column using COUNT(DISTINCT column_name).

To count the number of distinct categories:

SELECT COUNT(DISTINCT Category)
FROM Products;

Output:

| COUNT(DISTINCT Category) |
|--------------------------|
|                        2 |