Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Should I use COUNT(*) to get all the records in MySQL?
Whenever you want all the values like not null for a column then use count(*). This is faster than using count() method.
The syntax to use count(*) is as follows −
select count(*) as anyVariableName from yourTableName;
To understand the above concept, let us first create a table. The query to create a table is as follows −
mysql> create table CountingDemo -> ( -> BookId int -> ); Query OK, 0 rows affected (0.60 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into CountingDemo values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into CountingDemo values(); Query OK, 1 row affected (0.17 sec) mysql> insert into CountingDemo values(200); Query OK, 1 row affected (0.12 sec) mysql> insert into CountingDemo values(300); Query OK, 1 row affected (0.16 sec) mysql> insert into CountingDemo values(); Query OK, 1 row affected (0.12 sec)
Display all records from the table using select statement. The query is as follows −
mysql> select *from CountingDemo;
Output
+--------+ | BookId | +--------+ | 100 | | NULL | | 200 | | 300 | | NULL | +--------+ 5 rows in set (0.00 sec)
Suppose your column does not have null value then count(*) and count() gives same result.
But in our example BookId column is having some null values. In this case, both count(*) and count() gives different results.
Here is the query that use count(*) −
mysql> select count(*) as AllValue from CountingDemo;
Output
+----------+ | AllValue | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
Her is the query that use count() and gives another result since it won’t consider counting null values. The query is as follows −
mysql> select count(BookId) as AllvalueWhichisNotnull from CountingDemo;
Output
+------------------------+ | AllvalueWhichisNotnull | +------------------------+ | 3 | +------------------------+ 1 row in set (0.00 sec)
95 Views
