Count rows in more than one table with tSQL

问题:

I need to count rows in more than one table in SQL Server 2008. I do this:

select count(*) from (select * from tbl1 union all select * from tbl2)

But it gives me an error of incorrect syntax near ). Why?

PS. The actual number of tables can be more than 2.

回答1:

In case you have different number of columns in your tables try this way

 SELECT count(*) 
 FROM (
      SELECT NULL as columnName 
      FROM tbl1 
          UNION ALL
      SELECT NULL 
      FROM tbl2
     ) T

回答2:

try this:

You have to give a name to your derived table

select count(*) from 
(select * from tbl1 union all select * from tbl2)a

回答3:

I think you have to alias the SELECT in the FROM clause:

select count(*) 
from 
(
   select * from tbl1 
   union all 
   select * from tbl2
) AS SUB

You also need to ensure that the * in both tables tbl1 and tbl2 return exactly the same number of columns and they have to be matched in their type.

回答4:

I don't like doing the union before doing the count. It gives the SQL optimizer an opportunithy to choose to do more work.

AlexK's (deleted) solution is fine. You could also do:

select (select count(*) from tbl1) + (select count(*) from tbl2) as cnt