SQL SELECT DISTINCT

SQL SELECT DISTINCT Statement

The SQL SELECT DISTINCT statement is used to retrieve unique values from a database table, eliminating duplicate entries. This command is essential for identifying unique records and ensuring data accuracy and integrity.

---

Syntax

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

• SELECT DISTINCT: This is the SQL keyword used to select unique values from specified columns.
• column1, column2, ...: These specify the names of the columns from which to retrieve unique values.
• FROM: This is the SQL keyword used to specify the table.
• table_name: This specifies the name of the table from which to retrieve the unique values.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the SELECT DISTINCT statement to retrieve unique values.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table, including duplicate entries.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-03-01');

Here, we insert three rows of data into the employees table, including a duplicate entry for 'John Doe'.

Step 4: Using the SELECT DISTINCT Statement

This step involves retrieving unique values from the first_name column using the SELECT DISTINCT statement.

SELECT DISTINCT first_name FROM employees;

This command will return unique values from the first_name column, eliminating duplicates.

To retrieve unique combinations of first_name and last_name:

SELECT DISTINCT first_name, last_name FROM employees;

This command will return unique combinations of the first_name and last_name columns, ensuring no duplicate pairs are included.