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.

No comments:

Post a Comment

Primitive Types in TypeScript

In TypeScript, primitive types are the most basic data types, and they are the building blocks for handling data. They correspond to simple ...