Detailed explanation of the usage example of COUNT in SQL

1. COUNT(*): Statistics of the total number of rows

COUNT(*)It is one of the most commonly used statistics methods in SQL. It counts the number of rows in the query result set (includingNULLvalue).

grammar:

SELECT COUNT(*) FROM Table name;

Example:Suppose we have oneemployeestable containing the following data:

Perform the following query:

SELECT COUNT(*) FROM employees;

result:

3

explain: COUNT(*)Will count all rows, not ignoreNULLvalue, even if some columns haveNULL, it will also calculate each row. Therefore, the result is3, means there are 3 rows of data in the table.

2. COUNT(1): Statistics of the total number of rows

COUNT(1)The role andCOUNT(*)Similarly, all rows in the table are counted. It is a small trick in SQL optimization that some database systems (such as MySQL) will optimize.

grammar:

SELECT COUNT(1) FROM Table name;

explain: COUNT(1)andCOUNT(*)The result is the same and will return the number of all rows in the table. The database will1Instead of being a constant to participate in the calculation, it does not care about the specific value of each column.

Optimization differences:In some database systems (such as MySQL),COUNT(1)andCOUNT(*)The performance may be the same in the execution plan, and there is basically no difference in query efficiency. However, some older database systems are processingCOUNT(*)When the data is parsed, the entire line of data will be parsed, andCOUNT(1)You can directly count the number of rows, so there will be some slight performance differences.

3. COUNT(Column name): Statistics the number of non-NULL values ​​in a specific column

andCOUNT(*)different,COUNT(Column name)Only the specified column will be countedNon NULLNumber of values. If there is a columnNULLvalue,COUNT(Column name)They will be skipped automatically.

grammar:

SELECT COUNT(List name) FROM Table name;

Example:

SELECT COUNT(age) FROM employees;

result

2

4. COUNT(DISTINCT column name): Count the number of non-NULL values ​​that are not repeated in a column

Sometimes, we need not only count non-NULL values ​​in a column, but also count non-repetitive values. In this case, it is possible to useCOUNT(DISTINCT column name)Come to complete this task.

grammar:

SELECT COUNT(DISTINCT List name) FROM Table name;

Example:Suppose we have the following table data:

Query:

SELECT COUNT(DISTINCT department) FROM employees;

result:

3

explain:There are three non-repeatable in the tabledepartmentvalue:Sales, HR, IT. therefore,COUNT(DISTINCT department)Return to 3, indicating that there are 3 different departments.

5. COUNT()andGROUP BYThe combination of

COUNT()Functions often andGROUP BYThe clause is used in conjunction with the count of the number of each group.GROUP BYGroup data according to a column.COUNT()The number of rows in each group is counted.

6. COUNTandHAVINGThe combination of

HAVINGThe clause is usually withGROUP BYUse together to filter the polymerization results. It can be used to limit the number of returned packets, and the conditions can be based onCOUNT()The result of the isogenetic function.

7. COUNTandJOINThe combination of

In complex queries,COUNT()Functions can be used withJOINUsed together with the clause to count the number of records matching another table.

Example:

SELECT , COUNT() 
FROM departments d 
LEFT JOIN employees e ON  = e.department_id 
GROUP BY ;

explain:This query returns the number of employees in each department, and even if some departments do not have employees, the department's records will be returned (because it is usedLEFT JOIN). useCOUNT()Count the number of employees associated in each department.

COUNT(*)andCOUNT(1)The difference

the difference：

• In most database systems,COUNT(*)andCOUNT(1)The performance is almost the same.COUNT(*)It counts all rows in the table without caring about the column content,COUNT(1)Then it is1Use as a fixed value for statistics.
• In some database systems,COUNT(1)Considered slightly more efficient because it doesn't require parsing all columns, just using constants1To count.

in conclusion: Modern databases (such as MySQL) will be correctCOUNT(*)andCOUNT(1)Optimization is done, so the performance differences between the two can be almost neglected. Which method to choose can be based on personal habits or project specifications.

Summarize

• COUNT(*): All rows in the statistics table, includingNULLvalue.
• COUNT(1):andCOUNT(*)Similarly, count all rows.
• COUNT(Column name): Statistics of non-in certain columnsNULLNumber of values.
• COUNT(DISTINCT column name): Statistics of non-repetitive non-NULLvalue.
• COUNTCan be used withGROUP BYandHAVINGCombining clauses such as the same are used to perform complex grouping statistics and conditional filtering.
• COUNT(*)andCOUNT(1)The performance is the same in most databases and can be used as usual.

This is the end of this article about the detailed explanation of COUNT usage examples in SQL. For more related content on SQL count usage, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!