Difference between UNION and UNION ALL

Following are the constraints that are associated with UNION and UNION ALL operator

Ø  The tables should contains the same number of columns

Ø  Columns in the tables must be of the same data type

Ø  Order by clause should be applied to the final result set

Ø  Column name for the result set would be of the first query mentioned

Union

UNION 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. And it is providing distinct result set.

Let’s see the example

Creating two table

create table Emp  (ID int, Name Varchar(50) ) create table Emp1 (ID int, Name Varchar(50) )

Now I am inserting the some records.

insert into Emp (ID,Name) values (1,'Bagesh') insert into Emp (ID,Name) values (2,'Kumar') insert into Emp (ID,Name) values (3,'singh') insert into Emp1 (ID,Name) values (1,'Bagesh') insert into Emp1 (ID,Name) values (2,'Kumar') insert into Emp1 (ID,Name) values (3,'singh') insert into Emp1 (ID,Name) values (4,'Rajesh') insert into Emp1 (ID,Name) values (5,'Mahesh') insert into Emp1 (ID,Name) values (6,'Ganesh')

  

UNION removes duplicate rows, UNION have to perform distinct sort to remove duplicates.

The distinct sort operation happening in UNION is time consuming. Let’s see the Estimated Execution Plan in SQL Server Management Studio (SSMS).

Click on the Estimated Execution plan icon.

 

See here 63% uses for shorting.

Both tables have same number of columns and data types. If not then it will be through error.

 

Union All

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.

It is faster than the UNION because it is not sorting the records. See the Execution plan

 

There is no cost for the sorting so it is faster than the UNION.

UNION and UNION ALL with TEXT Data Type

When we use a UNION for the TEXT type columns, SQL Server throws a runtime error.

create table Emp2  (ID int, DescT text ) create table Emp3 (ID int, DescT text )

Inserting some records

insert into Emp2 (ID, DescT) values (1,'This is Bagesh') insert into Emp2 (ID, DescT) values (2,'This is Kumar') insert into Emp2 (ID, DescT) values (3,'This is singh') insert into Emp3 (ID, DescT) values (1,'This is Bagesh') insert into Emp3 (ID, DescT) values (4,'This is Rajesh') insert into Emp3 (ID, DescT) values (5,'this is Mahesh')

When we use UNION we will get the below error

The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

 

Getting this error because UNION sort the record but it is not able to sort the TEXT data type.
Now see with the UNION ALL

Summary

UNION	UNION ALL
UNION removes duplicate rows.	“UNION ALL” does not remove the duplicate row. It returns all from all queries.
UNION uses a distinct sort	“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
UNION cannot work with a column that has a TEXT data type.	UNION ALL can work with all data type columns.
It is slower then Union all	It is faster than Union

Select INTO in SQL Server

Select into is used to make a new table which will contain the data that is the result set of the query in which it is used. Select Into is generally used to make a backup of a particular data set.

Syntax of Select into statement

SELECT expressions INTO New_table FROM table [WHERE conditions]

Parameters or Arguments

Expressions

Name of the columns or calculations that we want to retrieve.

New_table

The new table to create with the selected expressions and their associated definitions New_table must not exist in the database. If the table exists it will throw the errors.

Table

Name of the table from which we want retrieve the data. There must be at least one table listed in the FROM clause.

WHERE conditions

Where clause is Optional. If we want to select the record based on the condition then we need to use where. The conditions that must be met for the records to be selected.

Let’s see the example

Here I am using AdventureWorksDW2008R2

select * into Test1 from  DimEmployee

  

See the result.

  

Test1 table created on the database

  

See the records

  

Now see the example of where clause.

select * into Test2 from  DimEmployee where FirstName like 'a%'

I am creating a table Test2 which has only the list of employee whose Name start from A.

  

Where clause we are using for condition.

LIKE clause in SQL server

The LIKE operator is used to search for a specified pattern in a column. It is used to list the all rows in a table whose column value match a specified pattern. It is used when we want to search row to specific pattern or we don’t know the entire value. There are two wildcards used in conjunction with the LIKE operator:

Ø  The percent sign (%)

Ø  The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for a single character
[charlist]	Sets and ranges of characters to match
[^charlist] or [!charlist]	Matches only a character NOT specified within the brackets

Let’s see the example

Note: I am using AdventureWorksDW2008R2 Database and DimEmployee table

1.       Get all the employee who have the word Rob  anywhere in their First name

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where FirstName like '%Rob%'

            

2.       Get all the employee whose First Name start with the word Da

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where FirstName like 'Da%'

            

3.       Get the all employee whose name start from a, b, c or d

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where FirstName like '[a-d]%'   --where FirstName like '[abcd]%'   --where FirstName like '[a,b,c,d]%'   --You can use any of them.

           

4.       Get all the Employees whose First Name starts with the character a or b or c or d and second character must be a

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where FirstName like '[a-d][a]%'   --where FirstName like '[abcd][a]%'   --where FirstName like '[a,b,c,d][a]%'

            

5.       Get all the Employees whose Name is not starting with letter a or b or c or d

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where FirstName like '[^a-d]%'   --where FirstName like '[^abcd]%'   --where FirstName like '[a,b,c,d]%'

            

6.       Get all the Employees whose first Name must have 3^rd character  with letter a

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where FirstName like '__[a]%' --here 2 underscore  __

            

7.       Get all the Employees whose hire on 2001-01

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where HireDate like '2002-01%'

             

8.       Get the all employee whose Middle name is null

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where MiddleName like 'NULL%'

             

Above sql script is returning nothing. But we have Null value in Middle name. You may have confused why not it returning anything.

The reason behind it.

The above query didn’t return the employee whose is Middle name column value is NULL. As we know that the NULL means unknown value, it can’t be compared with a known value.

 For getting the desirer output we need to use below sql script.

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where MiddleName is null

            

9.       Get the all employee whose middle name is not null.

SELECT [EmployeeKey]       ,[FirstName]       ,[LastName]       ,[MiddleName]       ,[HireDate]       ,[BirthDate]       ,[MaritalStatus]       ,[Gender]       ,[StartDate]       ,[EndDate]       ,[Status]   FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]   where MiddleName is not null