Total Page Preview:   000000004684

Explain difference between Union and Union all in SQL in English and Hindi

 

Differnce between Union and Union all in SQL-Server in English 

Union operator:

Union operator in MySQL allows us to combine two or more results from multiple SELECT queries into a single result set. It has a default feature to remove the duplicate rows from the tables. This operator syntax always uses the column's name in the first SELECT statement to be the column names of the output.

 
MySQL Union must follow these basic rules:
  • The number and order of the columns should be the same in all queries.
  • The corresponding columns position of each select query must have a compatible data type.
  • The column name selected in the different SELECT queries must be in the same order.
  • The column name of the first SELECT query will be the column names of the output.

Union all operator:

The UNION ALL operator combines two or more results from multiple SELECT queries and returns all records into a single result set. It does not remove the duplicate rows from the output of the SELECT statements.
 
 
Union and Union All Example
Let us understand the differences between Union and Union All operators through an example. Suppose we have a table named "Student" and "Student2" that contains the following data:
 
Table: Student

Table: Student2

Following SQL statement returns the distinct name of cities from both tables using the UNION query:

After executing the above statement, we will get the below output because the Union operator returns only the distinct values.

Following SQL statement returns all cities name including duplicates from both tables using the UNION ALL query:

After executing the above statement, we will get the below output because the Union All operator returns whole records without eliminating distinct values.

 

Differnce between Union and Union all in SQL-Server in Hindi

Union aur Union All SQL mein do alag-alag SELECT statements ke results ko ek hi result set mein combine karne ke liye use kiye jaane wale operators hain. In dono ke beech ka antar table ke roop mein is tarah se hai:

 

 

 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments