NULL Values In SQL | SQL UNION Clause
Don't become Obsolete & get a Pink Slip
Follow DataFlair on Google News & Stay ahead of the game
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