How to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery in MySql?

Achieve this with the help of where clause.

The syntax is as follows

SELECT yourColumnName1,yourColumnName2,...N FROM yourTableName
WHERE
(
   SELECT COUNT(*) FROM yourTableName
)=2;

To understand the concept, let us create a table. The query to create a table is as follows

mysql> create table CountWithSubqueryDemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   - > EmployeeName varchar(20)
   - > );
Query OK, 0 rows affected (2.09 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into CountWithSubqueryDemo(EmployeeName) values('John');
Query OK, 1 row affected (0.54 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL);
Query OK, 1 row affected (0.36 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Carol');
Query OK, 1 row affected (0.23 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('');
Query OK, 1 row affected (0.31 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Bob');
Query OK, 1 row affected (1.24 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL);
Query OK, 1 row affected (0.30 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Sam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('Mike');
Query OK, 1 row affected (0.18 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values('James');
Query OK, 1 row affected (0.26 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from CountWithSubqueryDemo;

The following is the output

+----+--------------+
| Id | EmployeeName |
+----+--------------+
|  1 | John         |
|  2 | NULL         |
|  3 | Carol        |
|  4 |              |
|  5 | Bob          |
|  6 | NULL         |
|  7 | Sam          |
|  8 | Mike         |
|  9 | James        |
+----+--------------+
9 rows in set (0.19 sec)

The following is how you can use actual row count in where clause

mysql> select Id,EmployeeName from CountWithSubqueryDemo
   - > where
   - > (
   - > select count(*) from CountWithSubqueryDemo
   - > )=2;
Empty set (0.03 sec)

In the above query we are getting empty set because it returns the row if the table have two records only. Therefore, let us delete all records from the table and insert 2 records in the table.

The query is as follows

mysql> truncate table CountWithSubqueryDemo;
Query OK, 0 rows affected (1.95 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into CountWithSubqueryDemo(EmployeeName) values('James');
Query OK, 1 row affected (0.15 sec)
mysql> insert into CountWithSubqueryDemo(EmployeeName) values(NULL);
Query OK, 1 row affected (0.46 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from CountWithSubqueryDemo;

The following is the output

+----+--------------+
| Id | EmployeeName |
+----+--------------+
|  1 | James        |
|  2 | NULL         |
+----+--------------+
2 rows in set (0.00 sec)

Now the following is the query for row count in where clause

mysql> select Id,EmployeeName from CountWithSubqueryDemo
   - > where
   - > (
   - > select count(*) from CountWithSubqueryDemo
   - > )=2;

The following is the output

+----+--------------+
| Id | EmployeeName |
+----+--------------+
|  1 | James        |
|  2 | NULL         |
+----+--------------+
2 rows in set (0.00 sec)

Arjun Thakur

Published on 19-Mar-2019 13:13:59

Previous Page Print Page