Difference between select count (*) and select count (1)

Source: Internet
Author: User

Generally, the returned results are the same for select count (*) and select count (1 ).

If the table does not have a primary key, count (1) is faster than count,

If a primary key exists, the count (primary key) is the fastest when the primary key is used as the Count condition.

If your table has only one field, count (*) is the fastest

The results of Count (*) and count (1) are the same. They all include null statistics, while count (column) does not include null statistics.

1. Differences between select 1 and select *
The selelct constant from... corresponds to all rows, and returns only one value, that is, a constant. Therefore, it can only be used to determine whether there are or not (such as the exists clause ). Select * from... returns all columns of all rows.
The key to performance differences is your from and where clauses. For example, if you can use an index in your where condition, the performance of select 1 from... is obviously better than that of select * from.
2. Use of select sum (1)
Select count (*) returns the number of all records that meet the condition, which is the same as select sum (1)
However, sum () can be any number, either a negative number or a floating point number. The returned value is the input value n * The number of records meeting the condition M.

Difference between select count (*) and select count (1)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.