Count the Number of Rows in Two Tables Using a Single Query

There are several ways to simply count the number of rows in two tables using single query.

1. select ( select count(*) from Table1 )     + ( select count(*) from Table2 )         as total_rows from my_one_row_table

2.  select sum(rows)          as total_rows  from (    select count(*) as rows      from Table1    union all    select count(*) as rows      from Table2       ) as u 

   UNION ALL is necessary here, not UNION, to guard against the case when both tables have the same number of rows, because UNION would discard one of the duplicate counts! Note also that no GROUP BY is necessary in the main query, because all the rows (produced by the UNION ALL subquery) are considered one group.

3. We could also use a CROSS JOIN:

      select t1.rows + t2.rows            as total_rows    from (           select count(*) as rows           from Table1         ) as t1    cross join (            select count(*) as rows            from Table2               ) as t2  

   The cross join works because each derived table has only one row.

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.