Union all with Select *

UNION ALL combines the result set of two or more queries into a single result set. This result set includes all the rows that belong to all queries in the UNION ALL. And it is providing all records in a result set.

Read: Differencebetween UNION and UNION ALL

https://bageshkumarbagi-msbi.blogspot.com/2016/05/difference-between-union-and-union-all.html

Recently we got an issue. We have create two staging table, both table is having same columns. See below.

CREATE TABLE stg_product_p1   (      product_p1_id INT IDENTITY(1, 1),      product_name  VARCHAR(50),      price         DECIMAL(5, 2),      quantity      SMALLINT   );  CREATE TABLE stg_product_p2   (      product_p2_id INT IDENTITY(1, 1),      product_name  VARCHAR(50),      quantity      SMALLINT,      price         DECIMAL(5, 2)   );

Now inserting some records into this table.

INSERT INTO stg_product_p1 VALUES      ('pen',50,10),             ('Copy',500,20); INSERT INTO stg_product_p2 VALUES      ('pen',2,3),             ('Copy',5,10);

See the value in this table.

Now we want to union all both table and get the total sum of price.

We have written the query as below.

SELECT Sum(price) AS total FROM   (SELECT *         FROM   stg_product_p1         UNION ALL         SELECT *         FROM   stg_product_p2)x

This value is wrong. It should be as below 

Total : 550.00 +13.00 it should be 563.00.

Lets see if we swap the table

Aging we are getting the wrong value.

Ok, now we are using the columns name instead select *.

Ooh! Good. Now we get the expected result.

Let’s see why it’s happen.

 When we are combining our two queries using SELECT *, SQL Server is comparing the columns in the order that we originally created the tables in. This means that the “Price” column of the first query is being matched to the “Quantity” of the second query and vice versa.

See the column order in sql server for both table.

SELECT Object_name(id),  colorder,   * FROM   syscolumns WHERE  Object_name(id) IN ( 'stg_Product_P1', 'stg_Product_P2' )

 
One more thing when we are using the * and someone added a new column in on table then our query will be failed. See below.

alter table stg_Product_P1 add product_desc varchar(500)

See the table.  

Now running the union all.

Even though we are not using the newly added column for the calculation. Same case if some drop the column from the table then which is not using in the calculation throw an error.

Let’s see the query which we are using the column name.  

Getting the expected result without any issue.

Note: Always we need to use column name instead of select * .

 Thanks. Happy Reading! J