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.

NULL Values In SQL | SQL UNION Clause

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.

Null values in SQL

Null Values in SQL

Null Values in SQL

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,

Null Values in SQL

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:

Null Values in SQL

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

NULL Values In SQL | SQL UNION Clause

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

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

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 −

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