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.



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

```
 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.

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.

Sanjay Agarwal

Submit a Tip

Browse "Java" Tips

Browse All Tips

0 Comments (click to add your comment)

Comment and Contribute