Getting the entire records from Two tables for Count purposes SQL I'm just wondering if anyone could help on how to bring two columns from two tables together in one table. 

These table have id's but they are not linked although they have other columns that I can join them on. But for the sake of getting the count of individual ID's I'm not sure if inner or outer join will give me the result that I want.

Here are the tables:

TABLE A


ID
1
2
3
4
5


TABLE B


2
4
5
7
8
9
10
11
12


I should be able to retrieve:


select count(a.id) from the combined_table
select count(b.id) from the combined_table
If there's no relationship between them...

Table A is customers and Table B is employees, and you really want to know how many bodies were involved with a production of Spamalot! I would look at a query like

SELECT
    A.RC + B.RC AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
) A
CROSS JOIN
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) B;


Read that as Add the RC column from A to the same column in B. A and B are derived in line and will only ever have one value returned as we're computing the scalar count of all the rows.

I'm using a CROSS JOIN because I need everything from both sets. I could have done this as a UNION ALL between the counts. The ALL is important, otherwise UNION eliminates duplicate rows. A variant on B would be to use a COUNT in the outer query and just select all the IDs. Assuming the data types match, it'd work either way.

-- Option B

SELECT
    SUM(D.RC) AS TotalBodies
FROM
(
    SELECT COUNT_BIG(*) AS RC FROM dbo.A A
    UNION ALL
    SELECT COUNT_BIG(*) AS RC FROM dbo.B B
) D(RC);


or just done it inline from the SELECT

-- Option C
SELECT (SELECT COUNT_BIG(*) AS RC FROM dbo.A A) + (SELECT COUNT_BIG(*) AS RC FROM dbo.B B) AS TotalBodies;


Some of that may be database dependent but works fine for SQL Server.

SQLFiddle