NULL in subquery

NULL in subquery the subquery returns a single row, multiple rows, and null value. It is applicable to the comparison operators of single-row subqueries: =, >,=, <=, and! =. The comparison operators for multi-row subqueries are in, not in, any, and any. What is unavoidable in subqueries is that null values are returned. We will discuss this issue below. In a single row subquery

SQL> select last_name,department_id from  employees where department_id is null;LAST_NAME                 DEPARTMENT_ID-------------------------                 -------------Grant

 

Check department_id In the employees table. It has a null value.

SQL> select last_name,department_id from  employees    where department_id=(select department_id from employees where last_name='Haus');no rows selected

 

The last_name of no row in the employees table is equal to Haus, so the subquery returns null. The above query knows that department_id has a null value. Isn't it displayed? This is because the value of the entire expression is NULL when any operation (such as addition, subtraction, multiplication, division, and string connection) includes NULL values. Use a single row function to process the NULL value. The result is also NULL (except for functions such as NVL ). 2. In multi-row subquery

SQL> select employee_id,manager_id from employees   where manager_id is null;EMPLOYEE_ID   MANAGER_ID----------- ---          -------        100

 

First, check manager_id In the employees table. It has a null value.

SQL> select  last_name  from employees  where  employee_id  not in (select manager_id from employees );no rows selected

 

Why is this not displayed? Let's take a look at in.

SQL> select  last_name  from employees emp where emp.employee_id  in (select manager_id from employees);LAST_NAME-------------------------CambraultDe HaanErrazurizFripp

 

Here, in can be used to display. This is because the not in and in operations are in essence OR operations, but the NULL processing method is different in the calculation logic OR, and the results are also different. To illustrate the problem above, let's look at the rule of NULL in the Boolean budget: Since NULL is unknown, therefore, the values of null and null, null or null, null and true, and null or false are unknown, AND these results are still NULL. So why does null and false and null or true get a definite result? Still consider from the concept of NULL. NULL is unknown, but the current NULL type is Boolean, so NULL can only be one of TRUE or FALSE. Therefore, the result of null and false is FALSE, AND the result of null or true is TRUE. With this, we can explain the problem above.

select  last_name  from employees  where  employee_id  not in (select manager_id from employees );

 

The subquery select manager_id from employees generates many values and has a NULL value. The relationship between them is OR. If manager_id is 10, 11, and NULL, you can change the above sentence:

select  last_name  from employees  where  employee_id  not in （10,11，null);

 

Where employee_id not in (10, 11, null) is equivalent to where no (employee_id = 10 or employee_id = 11 or employee_id = null). Use employee_id = 12 as an example. Not (12 = 10 or 12 = 11 or 12 = null) not (false or null) (OR operation, null is unknown, so the entire expression is null) not null (this is a Boolean operation. NULL indicates an unknown meaning. After a NOT operation is added, NULL cannot be changed to a definite value. If it is TRUE, not true will be changed to FALSE, if it is FALSE, not false will be changed to TRUE, the value is NOT sure, so it is null) null so the result is displayed as no rows selected

select  last_name  from employees emp where emp.employee_id  in (select manager_id from employees);

 

According to the above method, assume that manager_id is 10, 11 and NULL, then the sentence is changed:

select  last_name  from employees  where  employee_id  not in （10,11，null);

 

Where employee_id in (10, 11, null) is equivalent to where (employee_id = 10 or employee_id = 11 or employee_id = null). Let's take the description of employee_id = 10. (10 = 10 or 10 = 11 or 10 = null) (true or false or null) (OR has a true expression) true, so in can be found.