How to Count Rows from Multiple Tables in MySQL Using Subqueries?

Counting Rows from Multiple Tables in MySQL using Subqueries

Determining the number of rows across multiple tables is a common task in database analysis. MySQL provides the COUNT(*) function for counting rows, but when it comes to multiple tables, a different approach is required.

To count rows from multiple tables, subqueries can be employed. Each subquery selects the count of rows from a specific table based on specified conditions. The results of the subqueries are then combined using a single SELECT statement.

Consider the following example, where we want to count rows from three tables (table1, table2, and table3) where a specific condition is met:

SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition;
SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition;
SELECT COUNT(*) AS table3Count FROM table3 WHERE someCondition;

To display these counts in a single row, we can use the following query:

SELECT
  (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, 
  (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count,
  (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count;

This query will return the following result:

+-------------+-------------+-------------+
| table1Count | table2Count | table3Count |
+-------------+-------------+-------------+
| 14          | 27          | 0           |
+-------------+-------------+-------------+

By utilizing subqueries, we can easily count rows from multiple tables and present the results in a consolidated manner.

The above is the detailed content of How to Count Rows from Multiple Tables in MySQL Using Subqueries?. For more information, please follow other related articles on the PHP Chinese website!