How to perform query deduplication in oracle

In Oracle, you can use the SELECT statement and the DISTINCT keyword to achieve query deduplication, filter duplicate rows in the query result set, and ensure that the value of the specified column or columns returned after the query operation is unique; The syntax is "SELECT DISTINCT field name FROM data table name;".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

In Oracle, you can use the SELECT statement and the DISTINCT keyword to achieve query deduplication.

Oracle SELECT DISTINCT Statement

Use the DISTINCT clause in the SELECT statement to filter duplicate rows in the result set. It ensures that the values ​​returned in the specified column or columns in the SELECT clause are unique.

The following explains the syntax of the SELECT DISTINCT statement:

SELECT DISTINCT
  column_name
FROM
  table_name;

In the above syntax, the column_name column of the table_name table The values ​​in will be compared to filter out duplicates.

To retrieve unique data based on multiple columns, simply specify a list of columns in the SELECT clause, as follows:

SELECT
    DISTINCT column_1,
    column_2,
        ...
FROM
    table_name;

In this syntax, ## The combination of values ​​in #column_1, column_2, and column_n is used to determine the uniqueness of the data. The

DISTINCT clause can only be used in the SELECT statement.

Please note that

DISTINCT is not a synonym for the SQL standard UNIQUE. It is a good habit to always use DISTINCT instead of UNIQUE.

Oracle DISTINCT Example

Here are some examples of how to use

SELECT DISTINCT to see how it works.

1. Example of Oracle DISTINCT applied on one column

View the Contacts (

contacts) table in the sample database:

The following example retrieves the names of all contacts:

SELECT first_name
FROM contacts
ORDER BY first_name;

Execute the above query statement and get the following results-

The query returned

319 rows, indicating that the contact (contacts) table has 319 rows.

To obtain unique contact names, you can add the

DISTINCT keyword to the SELECT statement above, as follows:

SELECT DISTINCT first_name
FROM contacts
ORDER BY first_name;

Execute the above query statement and get the following results -

The query returned

302 rows, indicating the contact (contacts) table There are 17 rows that are duplicates and they have been filtered.

2. Oracle DISTINCT application multi-column example

Look at the

order_items table below. The structure of the table is as follows:

The following statement selects different product IDs and quantities from the

order_items table:

SELECT
    DISTINCT product_id,
    quantity
FROM
    ORDER_ITEMS
ORDER BY product_id;

Execute the above query statement and get the following results-

In this example, the values ​​of the

product_id and quantity columns are both used to evaluate the uniqueness of the rows in the result set.

3. Oracle DISTINCT and NULL

DISTINCT treats NULL values ​​as duplicate values. If you use the SELECT DISTINCT statement to query data from a column with multiple NULL values, the result set contains only one NULL value.

Please refer to the

locations table in the sample database, the structure is as follows -

The following statements are from

stateRetrieve data with multiple NULL values ​​in the column:

SELECT DISTINCT state
FROM locations
ORDER BY state NULLS FIRST;

Execute the above example code and get the following results-

As As you can see in the picture above, only a

NULL value is returned.

Recommended tutorial: "

Oracle Tutorial"

The above is the detailed content of How to perform query deduplication in oracle. For more information, please follow other related articles on the PHP Chinese website!