Requires Free Membership to View
There are several ways to approach this. Here is the most non-convoluted:
select ( select count(*) from Table1 )
+ ( select count(*) from Table2 )
as total_rows
This query uses two scalar subqueries, and adds the two scalar values tegether for the answer. This query is also missing the FROM clause, and while my knowledge of the SQL standard is sketchy, I know this is invalid, because I've looked this up once before, in Counts from two tables in one SQL statement (13 August 2004). Whenever I have doubt about whether some query is valid SQL, I always use the Mimer SQL Validator, and even in SQL-2003, you must apparently have a FROM clause. Nevertheless, the query does work in both MySQL and Microsoft SQL Server!
If you happen to have a one-row table lying around, such as Oracle's DUAL, you can simply throw that into the query. Note that you don't actually have to select anything from it! If you're not using Oracle, you should create your own one-row table, as it will have other handy uses (which are not relevant here).
select ( select count(*) from Table1 )
+ ( select count(*) from Table2 )
as total_rows
from my_one_row_table
You could also add up the two separate counts in a UNION query:
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.
Finally, you 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. As the previous article noted, you could also obtain a cross join by replacing the words CROSS JOIN with a comma, but that's the "old school" syntax.
This was first published in March 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation