I have two tables, each of them have following fields: ID, PartNumber,.. I simply want to count the rows from both...
tables in one SQL expression. For example, if there are 20 rows in Table1 and 21 rows in Table2, what I want as a result is Sum:41!
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.