SQL Day 17: Union and Union All

LO1: Purpose of Union and Union All

LO2: Difference between Union and Union All

LO3: Difference between Joins and Union

Prerequisite: Joins

Union and Union All is used to combine the result set of two or more queries.
To combine two results set, the column list in the select columns, data types, and order of the columns need to be same.

Let’s say we have the two table tblcustomer1 and tblcustomer2 with the identical columns in each table.



To combine these two result set into one we can use the Union All.

select * from tblcustomer1
Union All
select * from tblcustomer2



In the above screen, all the rows combined into one result set.

Now let’s try to use the Union only and observe the result set.

select * from tblcustomer1
Union
select * from tblcustomer2



In the above screen, the duplicate row is included only once and the result also gets sorted.

Note: In the case of Union, Duplicate rows only get removed from the result when all the columns contain the same the data.

If there is a difference between any column data then these rows will be included in the result.

Difference between Union and Union All

1. Union removes the duplicate rows from the result set where as Union All does not.
2. Union execution is slower than the Union All because Union has to perform the distinct 
sort to remove the duplicate rows.

Sorting the result of Union and Union All

To sort the result of Union and Union All, we can use the Order By clause at the last of the select statement. On using the Order By clause other than the last select statement then SQL Server will throw an error.

select * from tblcustomer1
Union All
select * from tblcustomer2
Order By Name desc



Difference between the Unions and Joins

Union combines rows from two or more tables, where as Join combine columns from two or more tables.

Union combines the result set of two or more select statement into one result set where as Join retrieve the data from two or more tables based on the logical relationship between the tables.

That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading