- 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.
- 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