NULL Values In SQL | SQL UNION Clause

1. Objective

In this SQL tutorial, we will see the Null values in SQL. Moreover, we will define the Null Values in SQL and also see IS NULL and IS NOT NULL with the help of syntax and example. Also, we will learn the Union clause in SQL. In this Union Clause, we will see Union ALL Clause and SQL Union ALL with WHERE Clause.
So, let us start with NULL Values in SQL.

2. NULL Values in SQL

A NULL field is a field in SQL which has no value. SQL supports NULL, a special value that is employed to represent the values of attributes that will be unknown or not apply to a tuple. It is necessary to know that a NULL value is completely different from zero value.
Do you know about SQL Operators
A NULL value is employed to represent a missing value. However, that it usually has one of 3 completely different interpretations:

• Value unknown (value exists, however, isn’t known).
• Value not available (exists, however, is purposely withheld).
• Attribute not applicable (undefined for this tuple).
• It is typically not possible to determine that of the meanings is meant. Hence, SQL doesn’t distinguish between the various meanings of NULL.

In general, every NULL value is taken into account to vary from each alternative NULL in the database. Once a NULL is concerned in a comparison operation, the results thought-about to be UNKNOWN. Hence, SQL uses a three-valued logic with values True, False and Unknown.

3. How to check for NULL Values in SQL?

SQL permits queries that check whether or not an attribute value is NULL. Instead of using = or to compare an attribute value to NULL, SQL uses IS and isn’t. This can be as a result of SQL considers every NULL value as being distinct from each alternative NULL value, thus equality comparison isn’t applicable.
Let’s revise SQL RDBMS Concept
Examples of Null Values in SQL:
Consider the following employee Table,

a. Queries in NULL Values

Find the Fname, Lname of the employee having no Super_ssn.

SELECT Fname, Lname
FROM worker
WHERE Super_ssn IS NULL;

Output:

Find the Count of the number of an employee having Super_ssn.

SELECT COUNT(*) AS Count
FROM worker
WHERE Super_ssn is not NULL;

4. UNIONS Clause in SQL

The SQL UNION clause/operator is used when there needs to be a combination of 2 or more SELECT statement without any duplicate rows.
Have a look at SQL RDBMS Database System

The syntax of UNION Clause in SQL:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

To use this UNION clause, every select statement should have

• The same number of expressions and columns selected.
• The same data type and.
• Have them within the same order but they have not had to be within the same length.

Example of SQL UNION Clause
Check the following tables.
Table 1 − Customers Table

ID  NAME	check for NULL Values AGE	ADDRESS	SALARY
1 Ramesh	32	Ahmedabad	2000
2 Khilan	25	Delhi	1500
3 Kaushik	23	Kota	2000
4  Chaitali	25	Mumbai	6500
5 Hardik	27	Bhopal	8500
6 Komal	22	MP	4500
7 Muffy	24	Indore	10000

Let’s revise SQL Expressions
Table 2 − ORDERS Table is as follows.

OID	DATE	CUSTOMER ID	AMOUNT
102	2009-10-08	3	3000
100	2009-10-08	3	1500
101	2009-11-20	2	1560
103	2008-05-20	4	2060

Now, let us join these two tables in our SELECT statement as follows −

SQL> SELECT  ID, NAME, AMOUNT, DATE
  FROM CUSTOMERS
  LEFT JOIN ORDERS
  ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
  SELECT  ID, NAME, AMOUNT, DATE
  FROM CUSTOMERS
  RIGHT JOIN ORDERS
  ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result −

ID	NAME	AMOUNT DATE
1	RAMESH	NULL  NULL
2	KHILAN	1560  2009-11-20 00:00:00
3	KAUSHIK	3000  2009-10-08 00:00:00
3	KAUSHIK	1500  2009-10-08 00:00:00
4	CHAITALI	2060 2008-05-20 00:00:00
5	HARDIK	NULL  NULL
6	KOMAL	NULL  NULL
7	MUFFY	NULL  NULL

a. The UNION ALL Clause

The UNION ALL operator is employed to combine the results of 2 select statements as well as duplicate rows.
The same rules that apply to the UNION clause can apply to the UNION ALL operator.
Do you know about SQL Create and Drop Database
The Syntax of UNION ALL Clause
The basic syntax of the UNION ALL is as follows.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

b. SQL UNION ALL With WHERE

The following SQL statement selects all German cities (duplicate values also) from “Customers” and “Suppliers”:
Example of SQL UNION Clause:

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

So, this was all in SQL NULL values. Hope you like our explanation of UNION Clause in SQL.

5. Conclusion

In this SQL Clause tutorial, we studied about the NULL and UNION clause on detail. Moreover, we saw the definition and meaning of NULL Values in SQL. Along with this, we look at IS NULL and IS NOT NULL in SQL. Also, we discussed UNION Clause in SQL, in which we learned SQL UNION ALL and SQL UNION ALL with WHERE Clause. Still, if any doubt regarding Null Values in SQL, ask in the comment tab.
See also – 
SQL Data Types
For reference