usage of distinct in oracle

The DISTINCT operator removes duplicate rows from a result set and can be used to extract unique values ​​or eliminate duplicate rows in join results. It can be applied to a single table, use multiple tables to eliminate duplicate rows, or use multiple columns as parameters. DISTINCT may affect query performance because it does not utilize indexes and may increase processing time.

DISTINCT Usage in Oracle

The DISTINCT operator is used to remove duplicate rows from the result set. It can be used to extract unique values ​​in a table, or to eliminate duplicate rows when joining results from multiple tables.

Syntax:

<code>SELECT DISTINCT column_name(s)
FROM table_name(s)
[WHERE condition(s)];</code>

How to use:

1. Remove duplicate rows from a single table:

<code>SELECT DISTINCT column_name
FROM table_name;</code>

For example, the following query will extract all unique "salary" column values ​​from the "employee" table:

<code>SELECT DISTINCT salary
FROM employee;</code>

2. From multiple Eliminate duplicate rows from each table:

<code>SELECT DISTINCT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;</code>

For example, the following query will extract all unique "name" column values ​​​​from the "employee" table and the "department" table, where "employee_id " Column matching:

<code>SELECT DISTINCT e.name, d.name
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id;</code>

3. Use multiple columns to remove duplicate rows:

You can use multiple columns as arguments to DISTINCT to eliminate duplicate rows that have Rows with the same combination of column values. The syntax is as follows:

<code>SELECT DISTINCT (column_name1, column_name2, ...)
FROM table_name;</code>

For example, the following query will extract all unique "(name, age)" combinations from the "student" table:

<code>SELECT DISTINCT (name, age)
FROM student;</code>

Notes:

• The DISTINCT operator is only used to remove duplicate rows from the result set; it does not guarantee that rows will be returned in any particular order.
• When using DISTINCT, the index is not fully utilized, which may affect query performance.
• DISTINCT may increase query processing time, especially for large data sets.

The above is the detailed content of usage of distinct in oracle. For more information, please follow other related articles on the PHP Chinese website!