How to Efficiently Count Rows from Multiple Tables in a Single Query?

Optimized multi-table row count query

In database management systems, it is often necessary to retrieve the number of rows from multiple tables. Although the UNION ALL operator can combine the results of separate queries, it does not always produce the expected output.

To solve this problem, a more efficient way is to use a subquery in the SELECT statement. This technique allows you to execute multiple queries and display the results in the desired format.

Query statement:

Consider the following query, which counts the number of rows in tables tab1 and tab2, and displays the results as two separate columns: Count_1 and Count_2:

<code class="language-sql">SELECT
    (
        SELECT COUNT(*)
        FROM   tab1
    ) AS count1,
    (
        SELECT COUNT(*)
        FROM   tab2
    ) AS count2
FROM
    dual</code>

Instructions:

• The outer SELECT statement creates a placeholder table named dual, which is used as a "Cartesian product" table. This table contains a row and a column, usually named "dummy" or "dual".
• The subqueries in brackets count the number of rows in tab1 and tab2 respectively.
The
• AS clause assigns the names count1 and count2 to the subquery results.
• The Cartesian product operation performed by the outer SELECT statement produces a row that effectively combines the two counts.

Result:

After executing the query, a table containing two columns Count_1 and Count_2 will be returned, containing the row counts of tab1 and tab2 respectively. This gives the expected output:

<code>Count_1   Count_2
123       456</code>

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