The difference between count1 and count* in oracle

The difference between COUNT(1) and COUNT(*) in Oracle is: COUNT(1) ignores null values ​​and only counts non-empty rows; COUNT(*) counts all rows, including null values; which function to choose Depends on: presence of null values, priority for performance or consistency.

The difference between COUNT(1) and COUNT(*) in Oracle

In Oracle, COUNT(1 ) and COUNT(*) are both aggregate functions used to count the number of records in a table, but there are subtle differences between them.

COUNT(1)

• Count only rows with non-null values.
• It prevents incorrect counting when null values ​​exist in the table.
• Because it ignores null values, it executes slightly faster than COUNT(*).

COUNT(*)

• Counts all rows, including those with null values.
• It returns an accurate count even if there are null values ​​in the table.
• Because it includes null values, the execution speed may be slightly slower than COUNT(1).

Which one to choose?

Choose COUNT(1) or COUNT(*) depends on the following factors:

• Whether there are null values:If there may be nulls in the table value, use COUNT(1) to avoid false counting.
• Performance: If speed is critical and you are sure there are no null values ​​in the table, you can use COUNT(1).
• Consistency: If you want a consistent count across all rows, including null values, you should use COUNT(*).

Example

Suppose there is a table named students that contains the following data:

<code>| id | name | age |
|---|---|---|
| 1 | John | 20 |
| 2 | NULL | 25 |
| 3 | Mary | 22 |</code>

If Query this table using COUNT(1), which will return the following results:

<code>SELECT COUNT(1) FROM students;
2</code>

This is because COUNT(1) ignores NULL values.

If you query this table using COUNT(*), it will return the following results:

<code>SELECT COUNT(*) FROM students;
3</code>

This is because COUNT(*) contains NULL values.

The above is the detailed content of The difference between count1 and count* in oracle. For more information, please follow other related articles on the PHP Chinese website!