NULL Values In SQL | SQL UNION Clause

We often require to combine the large volumes of data available to us in order to deal with day-to-day analysis and problems. Most of the time we have data spread in multiple tables.

To connect and collect all the data required by us, we often use various inbuilt functionalities.

In SQL, we use the Join and Union clause for the same. In this clause, we will be discussing the working and the usage of the Union clause and at the same time, we will see how we will be handling the null values present in our data.

Null values, if present in the data, can cause various logical and mathematical errors, so we need to be very cautious if we have null values in our database.

Let us move forward and discuss the Union clause in detail.

What is Union Clause in SQL and its Usage?

We use the union clause to get combined results from multiple select queries. We mostly use Union with two or more SQL select statements to combine the results they provide after querying the database.

Some points to keep in mind:

1. Select statements within the union statement must have the same number of columns.
2. All of the columns must be of the same datatype.
3. The order in the select statements should be maintained.

Syntax of SQL Union Clause:

SELECT * FROM tableName1
UNION
SELECT *  FROM tableName2;

Demo Database

Let us view our available tables in our database.
Table 1:

Table 2:

Types of Union Clause and Examples

Union Clause in SQL

When we use the union clause, it returns only the distinct values from the returned query result.

Syntax of SQL Union Clause:

SELECT * FROM tableName1
UNION
SELECT * FROM tableName2

Example 1: Let us view the collective post and name of employees.
Query:

SELECT  post FROM dataflair_emp1
UNION
SELECT name_emp as name FROM dataflair_emp2

SQL Union All Clause

When we use the union all clause, we get all the possible details, i.e., repetitions are also included. When we use only UNION duplicate data is excluded.

Syntax:

SELECT * FROM tableName1
UNION ALL
SELECT * FROM tableName2

Example 1: Let us collect the email id’s stored in both the DataFlair employee tables.
Query:

SELECT  email FROM dataflair_emp1
UNION All
SELECT email as name FROM dataflair_emp2
order by email

SQL Union with Where clause

When we use where with the union clause, we merge data that satisfies a condition. Conditions are passed in the where clause to get data according to the conditional statement.

Syntax:

SELECT * FROM tableName1 Where condition
UNION
SELECT * FROM tableName2 Where condition ;

Example 1: Let us find employee emails with age more than 27 and a salary less than 30000.
Query:

SELECT  salary,email FROM dataflair_emp1 Where salary < 30000
UNION All 
SELECT age,email as name FROM dataflair_emp2 where age > 27
order by email

Null values in UNION Clause

When we use Union the values which are null are automatically included in the result.
First Database after Modification:
Query :

SELECT * FROM DataFlair_emp1 ;

Example 1: Let us again try to view all the mail id’s after entering null values in our database.
Query:

SELECT  email FROM dataflair_emp1 
UNION All 
SELECT email as name FROM dataflair_emp2 
order by email

Summary

When we deal with voluminous data, we need certain combinations from time to time.

With the help of SQL Union clause, we can easily generate combinations of data with various conditions and orders.

Union provides us functionality to deal with both unique and duplicate types of data. When we deal with the union clause we need to take care of some points which we have already discussed above.

The essence lies in having the same type and same count of columns.