Oracle Live SQL - Script: How to work with NULL

Statement 1

Uncommissioned employees have NULL COMMISSION_PCT, so this query returns no rows

Find employees with no commission?

select   
  employee_id, first_name, last_name,   
  commission_pct   
from   hr.employees  
where  commission_pct = 0

no data found

Statement 2

Use the IS NULL condition to find rows storing NULL in COMMISSION_PCT

Find employees with no commission - fixed

select   
  employee_id, first_name, last_name,    
  commission_pct   
from   hr.employees   
where  commission_pct is null 
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
100 Steven King  - 
101 Neena Kochhar  - 
102 Lex De Haan  - 
103 Alexander Hunold  - 
104 Bruce Ernst  - 
105 David Austin  - 
106 Valli Pataballa  - 
107 Diana Lorentz  - 
108 Nancy Greenberg  - 
109 Daniel Faviet  - 

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
100	Steven	King	-
101	Neena	Kochhar	-
102	Lex	De Haan	-
103	Alexander	Hunold	-
104	Bruce	Ernst	-
105	David	Austin	-
106	Valli	Pataballa	-
107	Diana	Lorentz	-
108	Nancy	Greenberg	-
109	Daniel	Faviet	-

Statement 3

This returns no rows - so does this mean all employees manage a department?!

Find all employees that DON'T manage a department?

select * from hr.employees e  
where  e.employee_id not in (  
  select d.manager_id from hr.departments d  
)

no data found

Statement 4

There over a hundred employees

Count the employees

select count (*) from hr.employees


COUNT(*)
107

COUNT(*)
107

Statement 5

But only eleven managers! Some departments have no manager

Find the managers

select distinct manager_id from hr.departments


MANAGER_ID
108
200
203
204
121
145
103
 - 
201
114
100
205

12 rows selected.

MANAGER_ID
108
200
203
204
121
145
103
-
201
114
100
205

Statement 6

NOT IN is the same as checking EMPLOYEE_ID is not equal to all the department MANAGER_IDs. But some of these are NULL, so the whole WHERE evaluates to UNKNOWN and returns no rows.

NOT IN equivalent

select * from hr.employees e  
where  e.employee_id <> null  
and    e.employee_id <> 108  
and    e.employee_id <> 200 --etc.

no data found

Statement 7

To avoid this issue with NOT IN, you can add a check to ensure the comparison column IS NOT NULL

Find all employees that DON'T manage a department - fixed

select * from hr.employees e   
where  e.employee_id not in (   
  select d.manager_id from hr.departments d   
  where  d.manager_id is not null   
) 
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000  - 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000  - 100 90
104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000  - 103 60
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800  - 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800  - 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200  - 103 60
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000  - 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200  - 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700  - 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800  - 108 100

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
102	Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
104	Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
110	John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100

Statement 8

Or use NOT EXISTS

Find all employees that DON'T manage a department - fixed v2

select * from hr.employees e   
where  not exists (   
  select null from hr.departments d   
  where  e.employee_id = d.manager_id   
) 
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000  - 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000  - 100 90
104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000  - 103 60
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800  - 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800  - 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200  - 103 60
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000  - 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200  - 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700  - 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800  - 108 100

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
101	Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
102	Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
104	Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
105	David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60
109	Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
110	John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100

Statement 9

COUNT(*) returns the number of rows in the group. COUNT ( column ) returns the number of non-null rows in the group

COUNT(*) vs COUNT ( column )

select   
  count (*) emp#,   
  count ( commission_pct ) commissioned_emp#  
from   hr.employees


EMP# COMMISSIONED_EMP#
107 35

EMP#	COMMISSIONED_EMP#
107	35

Statement 10

Find the indexed columns on HR.EMPLOYEES

select   
  index_name,    
  listagg ( column_name, ', ' )   
    within group ( order by column_position ) indexed_cols  
from   all_ind_columns  
where  index_owner = 'HR'  
and    table_name = 'EMPLOYEES'  
group  by index_name


INDEX_NAME INDEXED_COLS
EMP_DEPARTMENT_IX DEPARTMENT_ID
EMP_EMAIL_UK EMAIL
EMP_EMP_ID_PK EMPLOYEE_ID
EMP_JOB_IX JOB_ID
EMP_MANAGER_IX MANAGER_ID
EMP_NAME_IX LAST_NAME, FIRST_NAME

6 rows selected.

INDEX_NAME	INDEXED_COLS
EMP_DEPARTMENT_IX	DEPARTMENT_ID
EMP_EMAIL_UK	EMAIL
EMP_EMP_ID_PK	EMPLOYEE_ID
EMP_JOB_IX	JOB_ID
EMP_MANAGER_IX	MANAGER_ID
EMP_NAME_IX	LAST_NAME, FIRST_NAME

Statement 11

COUNT(*) can use any index on mandatory columns

select   
  count (*)  
from   hr.employees


COUNT(*)
107

COUNT(*)
107

Statement 12

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))


PLAN_TABLE_OUTPUT
SQL_ID  5f9x9mpfsdfn9, child number 0
-------------------------------------
select      count (*)   from   hr.employees
 
Plan hash value: 3580537945
 
-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 5f9x9mpfsdfn9, child number 0
-------------------------------------
select count (*) from hr.employees

Plan hash value: 3580537945

-------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Cost (%CPU)\| Time \|
-------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| 1 (100)\| \|
\| 1 \| SORT AGGREGATE \| \| 1 \| \| \|
\| 2 \| INDEX FULL SCAN\| EMP_EMAIL_UK \| 107 \| 1 (0)\| 00:00:01 \|
-------------------------------------------------------------------------

Statement 13

If the column is optional, there must be an index on the column for COUNT ( column ) for the optimizer to use it.

COUNT optional column with index

select    
  count ( manager_id )   
from   hr.employees


COUNT(MANAGER_ID)
106

COUNT(MANAGER_ID)
106

Statement 14

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))


PLAN_TABLE_OUTPUT
SQL_ID  3916j7nns3ntf, child number 0
-------------------------------------
select       count ( manager_id )    from   hr.employees
 
Plan hash value: 3393209858
 
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |                |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| EMP_MANAGER_IX |   107 |   428 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 3916j7nns3ntf, child number 0
-------------------------------------
select count ( manager_id ) from hr.employees

Plan hash value: 3393209858

-----------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
-----------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 1 (100)\| \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 4 \| \| \|
\| 2 \| INDEX FULL SCAN\| EMP_MANAGER_IX \| 107 \| 428 \| 1 (0)\| 00:00:01 \|
-----------------------------------------------------------------------------------

Statement 15

COUNT optional column with no index

select   
  count ( commission_pct )  
from   hr.employees


COUNT(COMMISSION_PCT)
35

COUNT(COMMISSION_PCT)
35

Statement 16

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))


PLAN_TABLE_OUTPUT
SQL_ID  51gan22wzxvpz, child number 0
-------------------------------------
select      count ( commission_pct )   from   hr.employees
 
Plan hash value: 1756381138
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     2 |            |          |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   214 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 51gan22wzxvpz, child number 0
-------------------------------------
select count ( commission_pct ) from hr.employees

Plan hash value: 1756381138

--------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
--------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 3 (100)\| \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 2 \| \| \|
\| 2 \| TABLE ACCESS FULL\| EMPLOYEES \| 107 \| 214 \| 3 (0)\| 00:00:01 \|
--------------------------------------------------------------------------------

Statement 17

If the column is mandatory, the optimizer can use any index on mandatory columns as with COUNT (*)

COUNT mandatory column with no index

select    
  count ( hire_date )   
from   hr.employees


COUNT(HIRE_DATE)
107

COUNT(HIRE_DATE)
107

Statement 18

select *  
from   table(dbms_xplan.display_cursor(:LIVESQL_LAST_SQL_ID))


PLAN_TABLE_OUTPUT
SQL_ID  7ur0ak243yvaf, child number 0
-------------------------------------
select       count ( hire_date )    from   hr.employees
 
Plan hash value: 3580537945
 
-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------
 

14 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 7ur0ak243yvaf, child number 0
-------------------------------------
select count ( hire_date ) from hr.employees

Plan hash value: 3580537945

-------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Cost (%CPU)\| Time \|
-------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| 1 (100)\| \|
\| 1 \| SORT AGGREGATE \| \| 1 \| \| \|
\| 2 \| INDEX FULL SCAN\| EMP_EMAIL_UK \| 107 \| 1 (0)\| 00:00:01 \|
-------------------------------------------------------------------------

Statement 19

SUM and AVG discard NULL values. But adding NULL to a value returns NULL!

NULL in aggregates

select   
  /* Are these the same? */  
  sum ( commission_pct ) total_pct,   
  sum ( commission_pct + null ) total_pct_plus_null,   
  /* or these? */  
  avg ( commission_pct ) avg_pct,   
  avg ( commission_pct + null ) avg_pct_plus_null  
from   hr.employees


TOTAL_PCT TOTAL_PCT_PLUS_NULL AVG_PCT AVG_PCT_PLUS_NULL
7.8  - .2228571428571428571428571428571428571429  -

TOTAL_PCT	TOTAL_PCT_PLUS_NULL	AVG_PCT	AVG_PCT_PLUS_NULL
7.8	-	.2228571428571428571428571428571428571429	-

Statement 20

NULL + val = NULL

select 1 + null from dual


1+NULL
 -

1+NULL
-

Statement 21

In Oracle Database concatenating NULL to a string returns the original string

NULL string concatenation

select last_name || null   
from   hr.employees  
fetch first 10 rows only


LAST_NAME||NULL
Abel
Ande
Atkinson
Austin
Baer
Baida
Banda
Bates
Bell
Bernstein

10 rows selected.

LAST_NAME\|\|NULL
Abel
Ande
Atkinson
Austin
Baer
Baida
Banda
Bates
Bell
Bernstein

Statement 22

In Oracle Database the empty string is NULL; this is different from many other RDBMSes where the empty string is not null

The empty string

select * from dual  
where  '' is null


DUMMY
X

DUMMY
X

Statement 23

create table employees 
    ( employee_id    number(6) 
    , first_name     varchar2(20) 
    , last_name      varchar2(25) 
	 constraint     emp_last_name_nn  not null 
    , email          varchar2(25) 
	constraint     emp_email_nn  not null 
    , phone_number   varchar2(20) 
    , hire_date      date 
	constraint     emp_hire_date_nn  not null 
    , job_id         varchar2(10) 
	constraint     emp_job_nn  not null 
    , salary         number(8,2) 
    , commission_pct number(2,2) 
    , manager_id     number(6) 
    , department_id  number(4) 
    , constraint     emp_salary_min 
                     check (salary > 0)  
    , constraint     emp_email_uk 
                     unique (email) 
    )

Table created.

Statement 24

The check constraint EMP_SALARY_MIN stops you storing negative SALARY values

declare  
  negative_salary number := -999;  
begin  
  insert into employees (   
    employee_id, last_name, email, hire_date, job_id, department_id,  
    salary  
  ) values (   
    -99, 'Test', 'test@test.com', sysdate, 'ST_CLERK', 10,  
    negative_salary  
  );  
end;

ORA-02290: check constraint (SQL_NRHMCIYEFQQIIEANXUOVHAGDL.EMP_SALARY_MIN) violated ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 25

select  
  employee_id, first_name, last_name,  
  salary 
from   employees 
where  employee_id = -99

no data found

Statement 26

select search_condition 
from   user_constraints 
where  constraint_name = 'EMP_SALARY_MIN'


SEARCH_CONDITION
salary > 0

SEARCH_CONDITION
salary > 0

Statement 27

But the constraint doesn't prevent NULL salaries! This is because NULL > 0 => UNKNOWN. Check constraints only reject rows if the condition is FALSE.

declare  
  null_salary number;  
begin  
  insert into employees (   
    employee_id, last_name, email, hire_date, job_id, department_id,  
    salary  
  ) values (   
    -99, 'Test', 'test@test.com', sysdate, 'ST_CLERK', 10,  
    null_salary  
  );  
end;

Statement processed.

Statement 28

select   
  employee_id, first_name, last_name,  
  salary 
from   employees 
where  employee_id = -99


EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
-99  - Test  -

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY
-99	-	Test	-

Statement 29

The check constraint doesn't reject NULL salaries. But WHERE SALARY > 0 doesn't return them!

select   
  employee_id, first_name, last_name,   
  salary  
from   employees  
where  employee_id = -99  
and    salary > 0

no data found

Statement 30

rollback

Statement processed.

Statement 31

By default, ORDER BY uses ascending sorting with at the end

Sorting NULL ascending

select   
  employee_id, first_name, last_name,    
  commission_pct   
from   hr.employees   
order  by    
  commission_pct --nulls last   
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
164 Mattea Marvins .1
179 Charles Johnson .1
173 Sundita Kumar .1
167 Amit Banda .1
166 Sundar Ande .1
165 David Lee .1
155 Oliver Tuvault .15
172 Elizabeth Bates .15
171 William Smith .15
163 Danielle Greene .15

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
164	Mattea	Marvins	.1
179	Charles	Johnson	.1
173	Sundita	Kumar	.1
167	Amit	Banda	.1
166	Sundar	Ande	.1
165	David	Lee	.1
155	Oliver	Tuvault	.15
172	Elizabeth	Bates	.15
171	William	Smith	.15
163	Danielle	Greene	.15

Statement 32

Using a descending sort NULLs are first by default

Sorting NULL descending

select   
  employee_id, first_name, last_name,    
  commission_pct   
from   hr.employees   
order  by    
  commission_pct desc --nulls first   
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
100 Steven King  - 
101 Neena Kochhar  - 
102 Lex De Haan  - 
103 Alexander Hunold  - 
104 Bruce Ernst  - 
105 David Austin  - 
106 Valli Pataballa  - 
107 Diana Lorentz  - 
108 Nancy Greenberg  - 
109 Daniel Faviet  - 

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
100	Steven	King	-
101	Neena	Kochhar	-
102	Lex	De Haan	-
103	Alexander	Hunold	-
104	Bruce	Ernst	-
105	David	Austin	-
106	Valli	Pataballa	-
107	Diana	Lorentz	-
108	Nancy	Greenberg	-
109	Daniel	Faviet	-

Statement 33

Use the NULLS [ FIRST | LAST ] clause to override the default sorting behaviour for NULL

Control NULL position when sorting

select   
  employee_id, first_name, last_name,    
  commission_pct   
from   hr.employees   
order  by    
  commission_pct desc nulls last  
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
145 John Russell .4
156 Janette King .35
158 Allan McEwen .35
157 Patrick Sully .35
146 Karen Partners .3
160 Louise Doran .3
159 Lindsey Smith .3
150 Peter Tucker .3
148 Gerald Cambrault .3
147 Alberto Errazuriz .3

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
145	John	Russell	.4
156	Janette	King	.35
158	Allan	McEwen	.35
157	Patrick	Sully	.35
146	Karen	Partners	.3
160	Louise	Doran	.3
159	Lindsey	Smith	.3
150	Peter	Tucker	.3
148	Gerald	Cambrault	.3
147	Alberto	Errazuriz	.3

Statement 34

For parent rows with no children, an outer join returns NULL for all the columns of the child table

Outer joins

select    
  *   
from   hr.departments d   
left   join hr.employees e   
using  ( department_id )   
order  by employee_id desc  
fetch first 10 rows only


DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID
120 Treasury  - 1700  -  -  -  -  -  -  -  -  -  - 
210 IT Support  - 1700  -  -  -  -  -  -  -  -  -  - 
200 Operations  - 1700  -  -  -  -  -  -  -  -  -  - 
190 Contracting  - 1700  -  -  -  -  -  -  -  -  -  - 
180 Construction  - 1700  -  -  -  -  -  -  -  -  -  - 
170 Manufacturing  - 1700  -  -  -  -  -  -  -  -  -  - 
160 Benefits  - 1700  -  -  -  -  -  -  -  -  -  - 
150 Shareholder Services  - 1700  -  -  -  -  -  -  -  -  -  - 
140 Control And Credit  - 1700  -  -  -  -  -  -  -  -  -  - 
130 Corporate Tax  - 1700  -  -  -  -  -  -  -  -  -  - 

10 rows selected.

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID
120	Treasury	-	1700	-	-	-	-	-	-	-	-	-	-
210	IT Support	-	1700	-	-	-	-	-	-	-	-	-	-
200	Operations	-	1700	-	-	-	-	-	-	-	-	-	-
190	Contracting	-	1700	-	-	-	-	-	-	-	-	-	-
180	Construction	-	1700	-	-	-	-	-	-	-	-	-	-
170	Manufacturing	-	1700	-	-	-	-	-	-	-	-	-	-
160	Benefits	-	1700	-	-	-	-	-	-	-	-	-	-
150	Shareholder Services	-	1700	-	-	-	-	-	-	-	-	-	-
140	Control And Credit	-	1700	-	-	-	-	-	-	-	-	-	-
130	Corporate Tax	-	1700	-	-	-	-	-	-	-	-	-	-

Statement 35

COUNT (*) returns the number of rows in the group. There's always at least one row in each group. So this returns at least one for every row, even if there are no employees in a department.

Outer join grouping

select    
  department_id, count (*) c   
from   hr.departments   
left   join hr.employees   
using  ( department_id )   
group  by department_id   
order  by c nulls first  
fetch first 10 rows only


DEPARTMENT_ID C
10 1
180 1
170 1
160 1
40 1
140 1
130 1
120 1
70 1
150 1

10 rows selected.

DEPARTMENT_ID	C
10	1
180	1
170	1
160	1
40	1
140	1
130	1
120	1
70	1
150	1

Statement 36

When counting rows in outer joins, you almost always want to count a column from the inner table. This returns zero if the inner table has no matching rows

Outer join grouping - fixed

select    
  department_id, count ( e.employee_id ) c   
from   hr.departments d   
left   join hr.employees e   
using  ( department_id )   
group  by department_id   
order  by c nulls first  
fetch first 10 rows only


DEPARTMENT_ID C
210 0
220 0
140 0
260 0
200 0
250 0
240 0
180 0
270 0
190 0

10 rows selected.

DEPARTMENT_ID	C
210	0
220	0
140	0
260	0
200	0
250	0
240	0
180	0
270	0
190	0

Statement 37

Window functions return NULL when the window goes beyond the start or end of the data set. For LAG and LEAD this will be the first and last rows in each group respectively. This can also happen for other functions when the window excludes the current row

Window functions

select    
  employee_id, first_name, last_name, salary,  
  lag ( salary ) over ( order by hire_date ) prev_sal,  
  lead ( salary ) over ( order by hire_date ) next_sal,  
  avg ( salary ) over (   
    order by hire_date   
    rows between 10 preceding and 1 preceding   
  ) avg_prev_10_sals  
from   hr.employees  
where  department_id = 10


EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY PREV_SAL NEXT_SAL AVG_PREV_10_SALS
200 Jennifer Whalen 4400  -  -  -

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	PREV_SAL	NEXT_SAL	AVG_PREV_10_SALS
200	Jennifer	Whalen	4400	-	-	-

Statement 38

The third parameter of LAG and LEAD define the value to return when going beyond the start or end of the result set. There is no such option for functions like SUM, AVG, COUNT, etc.

Window functions

select    
  employee_id, first_name, last_name, salary,  
  lag ( salary, 1, 0 ) over ( order by hire_date ) prev_sal,  
  lead ( salary, 1, 0 ) over ( order by hire_date ) next_sal,  
  avg ( salary ) over (   
    order by hire_date   
    rows between 10 preceding and 1 preceding   
  ) avg_prev_10_sals  
from   hr.employees  
where  department_id = 10


EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY PREV_SAL NEXT_SAL AVG_PREV_10_SALS
200 Jennifer Whalen 4400 0 0  -

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	PREV_SAL	NEXT_SAL	AVG_PREV_10_SALS
200	Jennifer	Whalen	4400	0	0	-

Statement 39

There are many functions you can use to map NULLs to a value such as zero.

Mapping NULL to a value

with rws as (   
  select    
    employee_id, first_name, last_name,    
    nvl ( commission_pct, 0 ) nvl_comm_pct,    
    coalesce ( commission_pct, 0 ) coalesce_comm_pct,    
    nvl2 ( commission_pct, commission_pct, 0 ) nvl2_comm_pct,    
    decode ( commission_pct, null, 0, commission_pct ) decode_comm_pct,   
    case    
      when commission_pct is null then 0    
      else commission_pct    
    end case_comm_pct   
  from   hr.employees   
)   
  select * from rws  
  fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME NVL_COMM_PCT COALESCE_COMM_PCT NVL2_COMM_PCT DECODE_COMM_PCT CASE_COMM_PCT
100 Steven King 0 0 0 0 0
101 Neena Kochhar 0 0 0 0 0
102 Lex De Haan 0 0 0 0 0
103 Alexander Hunold 0 0 0 0 0
104 Bruce Ernst 0 0 0 0 0
105 David Austin 0 0 0 0 0
106 Valli Pataballa 0 0 0 0 0
107 Diana Lorentz 0 0 0 0 0
108 Nancy Greenberg 0 0 0 0 0
109 Daniel Faviet 0 0 0 0 0

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
100	Steven	King
101	Neena	Kochhar
102	Lex	De Haan
103	Alexander	Hunold
104	Bruce	Ernst
105	David	Austin
106	Valli	Pataballa
107	Diana	Lorentz
108	Nancy	Greenberg
109	Daniel	Faviet

Statement 40

COALESCE can accept many arguments. It returns the first of these that is non-null

First non-null in a list

select    
  employee_id, first_name, last_name,    
  commission_pct, manager_id, salary,   
  coalesce ( commission_pct, manager_id, salary ) coal   
from   hr.employees  
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT MANAGER_ID SALARY COAL
100 Steven King  -  - 24000 24000
101 Neena Kochhar  - 100 17000 100
102 Lex De Haan  - 100 17000 100
103 Alexander Hunold  - 102 9000 102
104 Bruce Ernst  - 103 6000 103
105 David Austin  - 103 4800 103
106 Valli Pataballa  - 103 4800 103
107 Diana Lorentz  - 103 4200 103
108 Nancy Greenberg  - 101 12008 101
109 Daniel Faviet  - 108 9000 108

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT	MANAGER_ID	SALARY	COAL
100	Steven	King	-	-	24000	24000
101	Neena	Kochhar	-	100	17000	100
102	Lex	De Haan	-	100	17000	100
103	Alexander	Hunold	-	102	9000	102
104	Bruce	Ernst	-	103	6000	103
105	David	Austin	-	103	4800	103
106	Valli	Pataballa	-	103	4800	103
107	Diana	Lorentz	-	103	4200	103
108	Nancy	Greenberg	-	101	12008	101
109	Daniel	Faviet	-	108	9000	108

Statement 41

Although this query searches on the primary key, because EMP_ID could be NULL the optimizer uses a full table scan.

COALESCE query performance

declare  
  emp_id number := 100;  
begin  
  for rws in (   
    select * from hr.employees  
    where  employee_id = coalesce ( to_number ( emp_id ), employee_id )  
  ) loop  
    null;   
  end loop;  
end;

Statement processed.

Statement 42

select *    
from   table(dbms_xplan.display_cursor('7d8nsu08g09fh'))


PLAN_TABLE_OUTPUT
SQL_ID  7d8nsu08g09fh, child number 0
-------------------------------------
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = COALESCE ( TO_NUMBER ( 
:B1 ), EMPLOYEE_ID )
 
Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    69 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("EMPLOYEE_ID"=COALESCE(TO_NUMBER(TO_CHAR(:B1)),"EMPLOYEE_I
              D"))
 

20 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 7d8nsu08g09fh, child number 0
-------------------------------------
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = COALESCE ( TO_NUMBER (
:B1 ), EMPLOYEE_ID )

Plan hash value: 1445457117

-------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
-------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 3 (100)\| \|
\|* 1 \| TABLE ACCESS FULL\| EMPLOYEES \| 1 \| 69 \| 3 (0)\| 00:00:01 \|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMPLOYEE_ID"=COALESCE(TO_NUMBER(TO_CHAR(:B1)),"EMPLOYEE_I
D"))

Statement 43

In some cases the optimizer can effectively expand and NVL into two queries. One where the first value is NULL, the other when it has a value. This produces a plan that can use an index if EMP_ID is provided and a full table scan if it's not.

NVL optimization

declare  
  emp_id number := 100;  
begin  
  for rws in (   
    select * from hr.employees  
    where  employee_id = nvl ( emp_id, employee_id )  
  ) loop  
    null;   
  end loop;  
end;

Statement processed.

Statement 44

select *    
from   table(dbms_xplan.display_cursor('9vhyyc4c3cu8s'))


PLAN_TABLE_OUTPUT
SQL_ID  9vhyyc4c3cu8s, child number 0
-------------------------------------
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = NVL ( :B1 , EMPLOYEE_ID )
 
Plan hash value: 71496665
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |       |       |     4 (100)|          |
|   1 |  VIEW                          | VW_ORE_B4851255 |   108 | 14364 |     4   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |                 |       |       |            |          |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |     1 |    69 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK   |     1 |       |     0   (0)|          |
|*  6 |    FILTER                      |                 |       |       |            |          |
|   7 |     TABLE ACCESS FULL          | EMPLOYEES       |   107 |  7383 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(:B1 IS NOT NULL)
   5 - access("EMPLOYEE_ID"=:B1)
   6 - filter(:B1 IS NULL)
 

26 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 9vhyyc4c3cu8s, child number 0
-------------------------------------
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = NVL ( :B1 , EMPLOYEE_ID )

Plan hash value: 71496665

--------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
--------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 4 (100)\| \|
\| 1 \| VIEW \| VW_ORE_B4851255 \| 108 \| 14364 \| 4 (0)\| 00:00:01 \|
\| 2 \| UNION-ALL \| \| \| \| \| \|
\|* 3 \| FILTER \| \| \| \| \| \|
\| 4 \| TABLE ACCESS BY INDEX ROWID\| EMPLOYEES \| 1 \| 69 \| 1 (0)\| 00:00:01 \|
\|* 5 \| INDEX UNIQUE SCAN \| EMP_EMP_ID_PK \| 1 \| \| 0 (0)\| \|
\|* 6 \| FILTER \| \| \| \| \| \|
\| 7 \| TABLE ACCESS FULL \| EMPLOYEES \| 107 \| 7383 \| 3 (0)\| 00:00:01 \|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(:B1 IS NOT NULL)
5 - access("EMPLOYEE_ID"=:B1)
6 - filter(:B1 IS NULL)

Statement 45

COALESCE stops evaluating its arguments as soon as it hits a non-null value. So this never runs 1/0 and returns a result

COALESCE Short-circuiting

select coalesce ( 1, 1/0 ) from dual


COALESCE(1,1/0)
1

COALESCE(1,1/0)
1

Statement 46

NVL always evaluates BOTH arguments. So this raises a divide by zero, even though the first value is non-null

NVL argument evaluation

select nvl ( 1, 1/0 ) from dual

ORA-01476: divisor is equal to zero

More Details: https://docs.oracle.com/error-help/db/ora-01476

Statement 47

This condition is UNKNOWN for rows where COMISSION_PCT is NULL. So it excludes uncommissioned employees.

Find all employees with less than 0.2% commission?

select   
  employee_id, first_name, last_name,   
  commission_pct   
from   hr.employees  
where  commission_pct < 0.2


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
155 Oliver Tuvault .15
163 Danielle Greene .15
164 Mattea Marvins .1
165 David Lee .1
166 Sundar Ande .1
167 Amit Banda .1
171 William Smith .15
172 Elizabeth Bates .15
173 Sundita Kumar .1
178 Kimberely Grant .15
179 Charles Johnson .1

11 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
155	Oliver	Tuvault	.15
163	Danielle	Greene	.15
164	Mattea	Marvins	.1
165	David	Lee	.1
166	Sundar	Ande	.1
167	Amit	Banda	.1
171	William	Smith	.15
172	Elizabeth	Bates	.15
173	Sundita	Kumar	.1
178	Kimberely	Grant	.15
179	Charles	Johnson	.1

Statement 48

To include rows where COMISSION_PCT is NULL, you can include a separate test for this

Find all employees with less than 0.2% commission - fixed v1

select    
  employee_id, first_name, last_name,    
  commission_pct    
from   hr.employees   
where  ( commission_pct < 0.2 or commission_pct is null )  
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
100 Steven King  - 
101 Neena Kochhar  - 
102 Lex De Haan  - 
103 Alexander Hunold  - 
104 Bruce Ernst  - 
105 David Austin  - 
106 Valli Pataballa  - 
107 Diana Lorentz  - 
108 Nancy Greenberg  - 
109 Daniel Faviet  - 

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
100	Steven	King	-
101	Neena	Kochhar	-
102	Lex	De Haan	-
103	Alexander	Hunold	-
104	Bruce	Ernst	-
105	David	Austin	-
106	Valli	Pataballa	-
107	Diana	Lorentz	-
108	Nancy	Greenberg	-
109	Daniel	Faviet	-

Statement 49

Alternatively you can use a function like NVL to map NULLs to a value such as zero

Find all employees with less than 0.2% commission - fixed v2

select    
  employee_id, first_name, last_name,    
  commission_pct    
from   hr.employees   
where  nvl ( commission_pct, 0 ) < 0.2  
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
100 Steven King  - 
101 Neena Kochhar  - 
102 Lex De Haan  - 
103 Alexander Hunold  - 
104 Bruce Ernst  - 
105 David Austin  - 
106 Valli Pataballa  - 
107 Diana Lorentz  - 
108 Nancy Greenberg  - 
109 Daniel Faviet  - 

10 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	COMMISSION_PCT
100	Steven	King	-
101	Neena	Kochhar	-
102	Lex	De Haan	-
103	Alexander	Hunold	-
104	Bruce	Ernst	-
105	David	Austin	-
106	Valli	Pataballa	-
107	Diana	Lorentz	-
108	Nancy	Greenberg	-
109	Daniel	Faviet	-

Statement 50

LNNVL returns all the rows where the condition is NOT TRUE. This includes NULL expressions evaluating to UNKNOWN!

LNNVL

select    
  employee_id, first_name, last_name,    
  commission_pct    
from   hr.employees   
where  lnnvl ( commission_pct >= 0.2 )  
fetch first 10 rows only


EMPLOYEE_ID FIRST_NAME LAST_NAME COMMISSION_PCT
100 Steven King  - 
101 Neena Kochhar  - 
102 Lex De Haan  - 
103 Alexander Hunold  - 
104 Bruce Ernst  - 
105 David Austin  - 
106 Valli Pataballa  - 
107 Diana Lorentz  - 
108 Nancy Greenberg  - 
109 Daniel Faviet  - 

10 rows selected.

How to work with NULL

Additional Information