How to Retrieve Row Counts from Multiple Tables in a Single SQL Row?

Efficiently Retrieving Row Counts from Multiple Tables in SQL

Getting row counts from multiple SQL tables and displaying them in a single row can be tricky. This example demonstrates how to retrieve counts from tab1 and tab2, presenting the results as:

<code>Count_1   Count_2
123       456</code>

Why UNION ALL Fails

A common, but flawed, approach uses UNION ALL:

<code class="language-sql">select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2</code>

This produces separate rows for each count, not the single-row output we need.

The Solution: Subqueries and the DUAL Table

The solution leverages subqueries and the DUAL table (a virtual table providing a single row). Here's the correct query:

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

Breakdown:

• Outer SELECT: This creates a single row to hold our results (count1 and count2).
• DUAL Table: Provides that single row for the outer SELECT to work with.
• Subqueries: Independently calculate the row counts for tab1 and tab2.
• AS Clauses: Assign meaningful names (count1, count2) to the result columns.

This method efficiently consolidates the counts from both tables into a single, neatly formatted row.

The above is the detailed content of How to Retrieve Row Counts from Multiple Tables in a Single SQL Row?. For more information, please follow other related articles on the PHP Chinese website!