How to remove duplicate data in oracle

In Oracle, you can use the Distinct keyword to remove duplicate data and filter duplicate rows in the result set. This keyword can ensure that the value of the specified column or columns returned in the SELECT clause 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 Distinct keyword to remove duplicate data.

Oracle Distinct keyword

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, table_namethe column_name# of the table ## Values ​​in columns 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

row means 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: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">SELECT DISTINCT first_name FROM contacts ORDER BY first_name;</pre><div class="contentsignin">Copy after login</div></div> 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: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">SELECT     DISTINCT product_id,     quantity FROM     ORDER_ITEMS ORDER BY product_id;</pre><div class="contentsignin">Copy after login</div></div>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

state

Retrieve data with multiple NULL values ​​in the column: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">SELECT DISTINCT state FROM locations ORDER BY state NULLS FIRST;</pre><div class="contentsignin">Copy after login</div></div>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 remove duplicate data in oracle. For more information, please follow other related articles on the PHP Chinese website!