SQL SELECT DISTINCT

Introduction

In this chapter, we will learn how to retrieve unique values from a table using the SELECT DISTINCT statement in SQL. The SELECT DISTINCT statement is used to return only distinct (unique) values from a table. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the SELECT DISTINCT statement effectively.

What is the SELECT DISTINCT Statement?

The SELECT DISTINCT statement is used to eliminate duplicate values from the result set. This is useful when you want to see only unique values from a column or a combination of columns.

Syntax for SELECT DISTINCT

Basic Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

• column1, column2, ...: The columns from which you want to retrieve unique values.
• table_name: The name of the table from which you want to retrieve data.

Example

Assume we have a table named employees:

SELECT DISTINCT last_name
FROM employees;

This command retrieves all unique last_name values from the employees table.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT
);

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email, department_id)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1),
('Sita', 'Patel', 'sita.patel@example.com', 2),
('Arjun', 'Singh', 'arjun.singh@example.com', 1),
('Priya', 'Sharma', 'priya.sharma@example.com', 2),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3);

3. Retrieve Unique Values from a Column

To retrieve unique last names from the employees table:

SELECT DISTINCT last_name
FROM employees;

Output

last_name
Kumar
Patel
Singh
Sharma

4. Retrieve Unique Combinations of Columns

To retrieve unique combinations of first_name and last_name from the employees table:

SELECT DISTINCT first_name, last_name
FROM employees;

Output

first_name	last_name
Ramesh	Kumar
Sita	Patel
Arjun	Singh
Priya	Sharma

5. Using DISTINCT with Other Clauses

You can use the DISTINCT keyword with other clauses like WHERE, ORDER BY, and GROUP BY.

Example with WHERE Clause

To retrieve unique last names where the department ID is 2:

SELECT DISTINCT last_name
FROM employees
WHERE department_id = 2;

Example with ORDER BY Clause

To retrieve unique last names and sort them alphabetically:

SELECT DISTINCT last_name
FROM employees
ORDER BY last_name ASC;

Example with GROUP BY Clause

To retrieve unique department IDs and count the number of employees in each:

SELECT department_id, COUNT(DISTINCT last_name)
FROM employees
GROUP BY department_id;

Output

department_id	COUNT(DISTINCT last_name)
1	2
2	2
3	1

Conclusion

The SELECT DISTINCT statement is used for retrieving unique values from a table, which is useful for eliminating duplicates and understanding the distinct entries in your data. This chapter covered the basic syntax, retrieving unique values from a single column, multiple columns, and using DISTINCT with other SQL clauses. Understanding how to use the SELECT DISTINCT statement effectively will enhance your ability to query and analyze your database data.