NVL() is used to convert null in correlated query : Subquery Basics « Subquery « Oracle PL / SQL






NVL() is used to convert null in correlated query

    


SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL> create table job(
  2    empno              Number(3), -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /

Table created.

SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       48000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000,       58000,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      36000,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000,       53000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       85000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /

1 row created.

SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   'Painter');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   'Tester');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   'Dediator');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   'Chemist');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   'Accountant');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   'Manager');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   'Programmer');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(NULL,   'Developer');

1 row created.

SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

     EMPNO ENAME      HIREDATE    ORIG_SALARY CURR_SALARY R
---------- ---------- ----------- ----------- ----------- -
       122 Alison     1996-MAR-21       45000       48000 E
       123 James      1978-DEC-12       23000       32000 W
       104 Celia      1982-OCT-24       53000       58000 E
       105 Robert     1984-JAN-15       31000       36000 W
       116 Linda      1987-JUL-30       43000       53000 E
       117 David      1990-DEC-31       78000       85000 W
       108 Jode       1996-SEP-17       21000       29000 E

7 rows selected.

SQL>
SQL> select * from job;

     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
           Developer

8 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> --NVL() is used to convert null in correlated query
SQL>
SQL> SELECT empno, ename
  2  FROM employee
  3  WHERE empno NOT IN
  4    (SELECT NVL(empno, 0)
  5     FROM job);

     EMPNO ENAME
---------- ----------
       108 Jode

SQL>
SQL>
SQL> drop table job;

Table dropped.

SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
           
         
    
    
    
  








Related examples in the same category

1.Use sub query as a virtual table
2.Compare with data from subquery
3.An example of a nested three-level subquery
4.If an inner query returns a NULL, the outer query also returns NULL
5.Working with multi-column subqueries
6.Use aggregate function in sub query
7.Sub query: 'SELECT 1 FROM dept d'
8.Using the EXISTS and NOT EXISTS operators
9.Writing Single Row Subqueries
10.Compare with the sub query result
11.Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
12.Subqueries in a FROM Clause (Inline Views)
13.Single Row Subqueries May Return a Maximum of One Row
14.Subqueries May Not Contain an ORDER BY Clause
15.Sub query with table join
16.Writing Multiple Column Subqueries with table join
17.Subquery just returns a literal value: improve performance of your query
18.EXISTS typically offers better performance than IN with subqueries
19.Writing Nested Subqueries
20.subqueries in the SELECT column list (New Way)
21.subqueries in the SELECT column list (Old way)
22.Not equals and subquery
23.Greater than average salary
24.Greater than max(salary)
25.Larger than value from subquery
26.Subquery in select statement
27.Subquery in where clause
28.Subqueries That Return Multiple Results
29.Subqueries in the WHERE Clause: equals
30.Subqueries in the WHERE Clause: less than
31.Simple Subqueries in select statement
32.Single-row subqueries return only one row of result.
33.The parent query of a single-row subquery can return more than one row.
34.Born after employee 4 was born
35.inline view: What percentage of these items exist in each bin selected