DISTINCT Clause in MySQL Workbench

• The `DISTINCT` clause in MySQL Workbench is used to retrieve unique values from a specific column or columns in a result set. It ensures that duplicate values are eliminated, and only distinct values are returned. The `DISTINCT` keyword is often used in conjunction with the `SELECT` statement to filter out duplicate rows.

Here's a detailed explanation of how the `DISTINCT` clause works, along with examples:

• Basic Syntax:

    SELECT DISTINCT columns

    FROM table;

• Examples: Consider a simple "orders" table with the following data:

    CREATE TABLE orders (

        order_id INT PRIMARY KEY,

        customer_id INT,

        order_date DATE

    );

    INSERT INTO orders (order_id, customer_id, order_date)

    VALUES

        (1, 101, '2023-01-05'),

        (2, 102, '2023-01-06'),

        (3, 101, '2023-01-08'),

        (4, 103, '2023-01-10'),

        (5, 102, '2023-01-11');

• Using DISTINCT with a Single Column: To retrieve unique values from a single column:

    SELECT DISTINCT customer_id FROM orders;

• This query would return unique `customer_id` values: 101, 102, and 103.

• Using DISTINCT with Multiple Columns: To retrieve unique combinations of values from multiple columns:

    SELECT DISTINCT customer_id, order_date FROM orders;

• This query would return unique combinations of `customer_id` and `order_date` values.

• Using DISTINCT with Expressions: You can also use the `DISTINCT` clause with expressions, not just column names.

    SELECT DISTINCT YEAR(order_date) AS order_year FROM orders;

• This query would return unique years extracted from the `order_date` values.

• Using DISTINCT with Multiple Columns: When using `DISTINCT` with multiple columns, it considers the combination of values in those columns to determine uniqueness.

    SELECT DISTINCT customer_id, order_date FROM orders;

• This query would return unique combinations of `customer_id` and `order_date` values.

• Combining DISTINCT with Other Clauses: You can use `DISTINCT` in combination with other clauses like `WHERE` and `ORDER BY`.

    SELECT DISTINCT customer_id FROM orders WHERE YEAR(order_date) = 2023 ORDER BY customer_id;

• The `DISTINCT` clause is useful when you want to identify unique values within a dataset. It's particularly handy when you're dealing with columns that might have duplicate entries, and you need to extract a list of distinct values for analysis or reporting purposes.