What is the usage of distinct in oracle?

Oracle's distinct usage can filter duplicate rows in the result set to ensure that the value of the specified column or columns returned in the "SELECT" clause is unique. The syntax is "SELECT DISTINCT column 1, column 2, column 3... from table name". "distinct" will sort the returned result set and can be used in conjunction with "order by" to improve efficiency.

#The operating environment of this article: Windows 10 system, Oracle version 19c, DELL G3 computer.

Oracle Distinct Usage

SELECT DISTINCT can be used to filter duplicate rows in the result set to ensure that the value of one or more columns specified in the SELECT clause is unique. .

Only use it to return the number of non-duplicate records, rather than use it to return all values ​​of non-duplicate records. The reason is that distinct can only be solved by double loop query, which will undoubtedly directly affect the efficiency of a website with a very large amount of data.

distinct will sort the returned result set, so it is best to use it in conjunction with order by to improve efficiency.

Oracle SELECT DISTINCT usage

The syntax of the SELECT DISTINCT statement is as follows:

SELECT DISTINCT
    column_1
        FROM
    table_name;

In the above syntax, the value in the column_1 column of the table_name table will Compare to filter duplicates.

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

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

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

The DISTINCT clause can only be used in a SELECT statement.

Please note that there is no difference between DISTINCT and UNIQUE in Oracle. They are synonyms. DISTINCT follows the ANSI standard, and UNIQUE is Oracle-specific. From a transplant perspective, it is better to use DISTINCT that follows the ANSI standard. s Choice.

Oracle DISTINCT Example

Let’s take a look at some examples of how to use SELECT DISTINCT to see how it works.

1. A simple example of Oracle DISTINCT

The following is a table

字段1     字段2
 id       name
 1         a
 2         b
 3         c
 4         c
 5         b

If you want to use one statement to query all data with non-duplicate names, you must use distinct Remove redundant duplicate records. So first enter:

select *, count（distinct name） from table group by name

Then we enter:

id name count（distinct name）

and get the result:

1 a 1
2 b 1
3 c 1

2. Examples of Oracle DISTINCT applied on a column

are as follows Example to retrieve 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 get unique contact names, you can add the DISTINCT keyword to the SELECT statement above, as shown below:

The query returned Row 302 means that 17 rows in the contacts table 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 statements are from order_items Select different product IDs and quantities in the 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 product_id and quantity columns Values ​​are used to evaluate the uniqueness of 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.

See the locations table in the sample database, the structure is as follows:

The following statement retrieves data with multiple NULL values ​​from the state column:

SELECT DISTINCT state
FROM locations
ORDER BY state NULLS FIRST;

Execute the above example code and get the following results:

As you can see in the picture above, only a NULL value is returned.

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