How can I count rows from multiple tables in MySQL using subqueries?

Counting Rows from Multiple Tables in MySQL

Determining row counts from multiple tables in MySQL is a common task in database development. This task may involve counting records from specific tables based on certain criteria.

Subquery Approach

One method to achieve this objective is by employing subqueries. A subquery is a nested SQL query that returns a single value or a table. In our case, we can utilize subqueries to retrieve the count of rows for each table and then combine them in a main query:

<code class="sql">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</code>

In this query, each subquery returns the row count for a specific table under a given condition. The outer query encapsulates these subqueries and assigns aliases to the result sets for clarity.

Executing the Query

Upon executing the above query, the following result is obtained:

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

This output provides the desired row counts for each table. It showcases that table1 has 14 rows meeting the specified condition, table2 has 27, and table3 has none.

The above is the detailed content of How can I count rows from multiple tables in MySQL using subqueries?. For more information, please follow other related articles on the PHP Chinese website!