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 theDISTINCTclause in theSELECTstatement to filter duplicate rows in the result set. It ensures that the values returned in the specified column or columns in theSELECTclause are unique.

The following explains the syntax of theSELECT DISTINCTstatement:

SELECT DISTINCT column_name FROM table_name;

In the above syntax,table_namethecolumn_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

SELECTclause, as follows:

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

In this syntax, ## The combination of values in #column_1

,column_2, andcolumn_nis used to determine the uniqueness of the data. The

DISTINCT

clause can only be used in theSELECTstatement.Please note that

DISTINCT

is not a synonym for the SQL standardUNIQUE. It is a good habit to always useDISTINCTinstead ofUNIQUE.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 has319rows.To obtain unique contact names, you can add the

DISTINCT

keyword to theSELECTstatement 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 are17rows 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

andquantitycolumns are both used to evaluate the uniqueness of the rows in the result set.3. Oracle DISTINCT and NULL

DISTINCT

treatsNULLvalues as duplicate values. If you use theSELECT DISTINCTstatement to query data from a column with multipleNULLvalues, the result set contains only oneNULLvalue.Please refer to the

locations

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

The following statements are from

state

Retrieve data with multipleNULLvalues 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 remove duplicate data in oracle. For more information, please follow other related articles on the PHP Chinese website!