How Can I Efficiently Combine COUNT(*) Results from Multiple Tables into a Single Row?

Combining COUNT(*) Results from Multiple Tables: A Comparison of UNION and Cartesian Product

Analyzing data across multiple tables often requires combining results. A frequent task is obtaining simultaneous row counts from different tables.

Let's say you need the row counts from tab1 and tab2. A UNION query might seem like the solution:

<code class="language-sql">SELECT COUNT(*) AS Count_1 FROM schema.tab1 UNION ALL SELECT COUNT(*) AS Count_2 FROM schema.tab2</code>

However, this produces a vertical result set, with Count_1 and Count_2 in separate rows. For a more organized, single-row output, consider a Cartesian product approach:

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

Here's how this query works:

• The dual table (a dummy table in many database systems) provides a single row, acting as a framework for the combined results.
• Subqueries independently calculate the COUNT(*) for each table.
• These subquery results are aliased as count1 and count2, creating a two-column, single-row output.

This method efficiently consolidates the row counts from both tables into a single, structured row, yielding the desired result:

<code>count1 | count2
-------+-------
  123  |   456</code>

The above is the detailed content of How Can I Efficiently Combine COUNT(*) Results from Multiple Tables into a Single Row?. For more information, please follow other related articles on the PHP Chinese website!