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 ); ( 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 |
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 * .
What a wonderful blog it really helped me to understand new things and contains some interesting topics so keep doing this work also have a look at this Best Infertility clinic in Faridabad
ReplyDelete