Search code examples
sql-server-2008countunionexcept

How to get COUNT when using EXCEPT and UNION

Why can not I use the COUNT in a table?

SELECT COUNT(*)  FROM
(
(SELECT * FROM task.tableA EXCEPT SELECT * FROM task.tableB)
UNION
(SELECT * FROM task.tableB EXCEPT SELECT * FROM task.tableA)
)
Solution

  • This type of query is called as inline view (Derived tables). An inline view is a SELECT statement in the FROM-clause of another SELECT statement.

    Benefits:

    1. We do not need to create the temporary table. This prevents the database from having too many objects, which is a good thing as each additional object in the database costs resources to manage.

    2. We can use a single SQL query to accomplish what we want.

    Execute your query with a table name "temp" as below

    SELECT COUNT(*) 
FROM
(
    (SELECT * FROM task.tableA EXCEPT SELECT * FROM task.tableB)
    UNION
    (SELECT * FROM task.tableB EXCEPT SELECT * FROM task.tableA)
) temp;