What is the usage of oracle distinct

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 What is the usage of oracle distinct, column What is the usage of oracle distinct, column What is the usage of oracle distinct... from table name". "distinct" will sort the returned result set and can be used in conjunction with "order by" to improve efficiency.

SELECT DISTINCT can be used to 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. This article will bring you SELECT Specific usage of DISTINCT.

Oracle SELECT DISTINCT usage

The syntax of the SELECT DISTINCT statement is as follows:

SELECT DISTINCT

column_What is the usage of oracle distinct
FROM
table_name;

In the above syntax, table_name The values ​​in column_What is the usage of oracle distinct of the table are compared to filter out duplicates.

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

SELECT DISTINCT column_What is the usage of oracle distinct,
 column_What is the usage of oracle distinct,
...
FROM
table_name;

In this syntax, the values ​​in column_What is the usage of oracle distinct, column_What is the usage of oracle distinct 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.

What is the usage of oracle distinct. Simple example of Oracle DISTINCT

The following is a table

字段What is the usage of oracle distinct 字段What is the usage of oracle distinct
id name
What is the usage of oracle distinct a
What is the usage of oracle distinct b
What is the usage of oracle distinct c
4 c
What is the usage of oracle distinct 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:

What is the usage of oracle distinct a What is the usage of oracle distinct
What is the usage of oracle distinct b What is the usage of oracle distinct
What is the usage of oracle distinct c What is the usage of oracle distinct

What is the usage of oracle distinct. Example of applying Oracle DISTINCT on a column

The following 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 What is the usage of oracle distinctWhat is the usage of oracle distinct9 rows, indicating that the contact ( contacts) table has What is the usage of oracle distinctWhat is the usage of oracle distinct9 rows.

To get unique contact names, you can add the DISTINCT keyword to the SELECT statement above, as shown below:

The query returned Row What is the usage of oracle distinct0What is the usage of oracle distinct means that What is the usage of oracle distinct7 rows in the contacts table are duplicates and they have been filtered.

What is the usage of oracle distinct. 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.

What is the usage of oracle distinct. Oracle DISTINCT and NULL

DISTINCT treats NULL values ​​as duplicate values. If using SELECT The DISTINCT statement queries data from a column with multiple NULL values, and 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 oracle distinct. For more information, please follow other related articles on the PHP Chinese website!