• 1,DUAL table
• 2,ROWID type
  • 2.1,Using ROWID to Query Data
  • 2.2,Updating data with ROWID
• 3,NULL value
  • 3.1,NULL and empty strings
  • 3.2,NULL and Functions
  • 3.3,NULL and Index
  • 3.4,NULL and SQL
• 4,summary

1, DUAL table

DUAL is a virtual table in Oracle, which is used to form the grammatical rules of SELECT. Oracle system guarantees that there will always be only one record in DUAL. My understanding of "the grammatical rules used to form SELECT" is that since SELECT statements in Oracle must contain FROM clauses (which is different from databases such as SQL Server), many times we only need to query variables or constants that are not related to any real table. At this time, if there is no virtual table such as DUAL to meet the grammatical rules, it is not easy to be true. Now. Oracle, which has always been famous for its power, certainly won't allow this defect to exist, so DUAL tables came into being at the historic moment. Example:

SELECT'A'res FROM DUAL; -- res:'A', query constant'A'
SELECT 1+2 res1,3*7 res2 FROM DUAL; -- res1:3,res2:21, do four operations, equivalent to a calculator
 SELECT fn_today res FROM DUAL; -- res: 2017-01-10, calling user functions
 SELECT ROWNUM res FROM DUAL; -- res: 1, call system functions
 SELECT SYSDATE res FROM DUAL; - res: 2017-02-05 21:31:05, query the current system time
 SELECT SYS_GUID() res FROM DUAL; -- res:'68A531826DEF4DA8BD7F03C1EE0C1A7E', to obtain a GUID
 SELECT DBMS_RANDOM.RANDOM res FROM DUAL; -- res: -212493338, get a random number
 SELECT USER res FROM DUAL; -- res: DEMO, query current connection username
 SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM DUAL; - res: HZZ-PC, query the current host name

2. ROWID Type

ROWID is a special and complex object in Oracle. Developers generally do not need to have a deep understanding of ROWID. ROWID is not only a data type, you can specify the field type to be ROWID when creating a field. ROWID is also the unique identifier of rows in Oracle tables. Through ROWID, Oracle can quickly locate the specific physical storage location of row data in Oracle tables.

2.1. Query data with ROWID

Some people call ROWID pseudo columns, but they have a distinct difference in grammar: ROWID can be qualified by tabular aliases. This is also well understood from the use of ROWID.

An example of querying a row of data in a ROWID table is given:

SELECT t.staff_id,t.staff_name,t.birthday FROM demo.t_staff t WHERE t.ROWID='AAAMpVAAEAAAABeAAA';

Result:

   STAFF_ID STAFF_NAME                                         BIRTHDAY
----------- -------------------------------------------------- -----------
          1 Xiao Ming                                               One thousand nine hundred and eighty-eight-05-08

Examples of duplicate data in ROWID query tables:

SELECT COUNT(1) res FROM demo.t_staff t WHERE t.ROWID>(SELECT MAX(n.ROWID) FROM demo.t_staff n WHERE n.staff_id=t.staff_id); -- res: 0

Examples of paging queries through ROWID (Paging employee tables, 3 pieces of data per page, page 1): uuuuuuuuuuuu

SELECT t4.staff_name,t4.dept_code,t4.gender,t4.birthday 
FROM demo.t_staff t4 
WHERE t4.ROWID IN(
  SELECT t3.rid FROM(
    SELECT t2.rid,ROWNUM rn FROM(
      SELECT t1.ROWID rid,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
    ) t2 WHERE ROWNUM <= (1*3)
  ) t3 WHERE t3.rn >= ((1-1)*3+1)
) ORDER BY t4.birthday;

Result:

STAFF_NAME                                         DEPT_CODE                                          GENDER BIRTHDAY
-------------------------------------------------- -------------------------------------------------- ------ -----------
Xiaosa 01021 1986-03-07
 Xiaoming 010101 1 1988-05-08
 Li Yang 010101 1 1989-01-14

2.2. Updating data with ROWID

In PL/SQL Developer, when querying a table, as long as ROWID is included in the list of query fields, the data that has been found can be directly modified. Writing examples:

SELECT t. ROWID, T. * FROM demo. t_staff; -- After the query is executed, a small lock appears on the data pane, which can be directly modified by clicking on it.

3, NULL value

NULL is a very special value in Oracle, and any type of value can be NULL. NULL is unknown, it can be any type of value, it can also exist independently of any type (literal NULL), any column without NOT NULL constraints or primary key constraints may have NULL values.

3.1, NULL and empty strings

NULL judgment and comparison rules: NULL and any value (including NULL itself) are neither equal nor do not want to wait, in other words, its comparison with any value is unknown. Many people say that NULL is equivalent to empty strings, but Oracle does not recommend that they be treated as the same, at least within some system functions they are different, and may change in the future. In fact, NULL may or may not be of any type, and empty strings must be of character type, which is the essential difference between them. Example:

WITH
t1 AS(SELECT COUNT(1) res FROM DUAL WHERE NULL=NULL),t2 AS(SELECT COUNT(1) res FROM DUAL WHERE NULL<>NULL),
t3 AS(SELECT COUNT(1) res FROM DUAL WHERE NULL=' '),t4 AS(SELECT COUNT(1) res FROM DUAL WHERE NULL<>' '),
t5 AS(SELECT COUNT(1) res FROM DUAL WHERE NULL=''),t6 AS(SELECT COUNT(1) res FROM DUAL WHERE NULL<>''),
t7 AS(SELECT COUNT(1) res FROM DUAL WHERE ''=''),t8 AS(SELECT COUNT(1) res FROM DUAL WHERE ''<>''),
t9 AS(SELECT COUNT(1) res FROM DUAL WHERE ''=' '),t10 AS(SELECT COUNT(1) res FROM DUAL WHERE ''<>' ')
SELECT t1.res,t2.res,t3.res,t4.res,t5.res,t6.res,t7.res,t8.res,t9.res,t10.res FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;

Results (The following results further prove NULL!= Any value & NULL!!= Any value and'<=> NULL):

       RES        RES        RES        RES        RES        RES        RES        RES        RES        RES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          0          0          0          0          0          0          0          0          0

In fact, we can't compare NULL with = and <>, nor can we compare NULL with other comparison operators such as >, < and so on. Oracle provides IS NULL and IS NOT NULL to determine whether a data is NULL or not. Example:

SELECT COUNT(1) res FROM DUAL WHERE NULL IS NULL; -- res: 1
SELECT COUNT(1) res FROM DUAL WHERE 0 IS NULL;    -- res: 0
SELECT COUNT(1) res FROM DUAL WHERE '' IS NULL;   -- res: 1
SELECT COUNT(1) res FROM DUAL WHERE NULL IS '';   -- Report errors(missing NULL keyword)

SELECT COUNT(1) res FROM DUAL WHERE NULL IS NOT NULL; -- res: 0
SELECT COUNT(1) res FROM DUAL WHERE 0 IS NOT NULL;    -- res: 1
SELECT COUNT(1) res FROM DUAL WHERE '' IS NOT NULL;   -- res: 0
SELECT COUNT(1) res FROM DUAL WHERE NULL IS NOT '';   -- Report errors(missing NULL keyword)

IS NULL and IS NOT NULL are inseparable and cannot be written as IS''or IS NOT', as can be proved by the last four cases in the two groups.

NULL arithmetic and logic operation rules: NULL in doing some arithmetic operations, such as +, -, *, / etc., the result must still be NULL. If the connection operator is |, NULL is ignored directly. Example:

SELECT 1 + NULL res FROM DUAL;    -- res: NULL
SELECT 1 - NULL res FROM DUAL;    -- res: NULL
SELECT 1 * NULL res FROM DUAL;    -- res: NULL
SELECT 1 / NULL res FROM DUAL;    -- res: NULL
SELECT '1' || NULL res FROM DUAL; -- res: '1'

If the NULL in the above five statements is replaced by'', the result will remain unchanged. This also confirms that Oracle has done the equivalent processing of''with NULL when doing logical operations.

3.2, NULL and Functions

Many functions in Oracle deal with NULL specially, such as aggregation function and DECODE function. Some functions ignore NULL directly, while others deal with NULL specially. Let's look at four examples of NULL and functions:

LTRIM function

SELECT LTRIM('_ID','_') res FROM DUAL;  -- res: ID
SELECT LTRIM('_ID',NULL) res FROM DUAL; -- res: NULL

REPLACE function

SELECT REPLACE('_ID','_') res FROM DUAL;  -- res: ID
SELECT REPLACE('_ID',NULL) res FROM DUAL; -- res: '_ID'

DECODE function
Grammar: DECODE (expr, val1, res1, val2, res2,...[, defval])
Description: If the first parameter is equal to the 2n (n >= 1) parameter, the 2n+1 parameter is returned. If the first parameter is not equal to all the 2n parameters, the default parameter (when the parameter list length is 2n, the last parameter is the default parameter) is returned to the default parameter and NULL is returned without the default parameter.
Example:

SELECT DECODE(1,1,'A',2,'B','C') res FROM dual;   -- res: 'A'
SELECT DECODE(2,1,'A',2,'B','C') res FROM dual;   -- res: 'B'
SELECT DECODE(3,1,'A',2,'B','C') res FROM dual;   -- res: 'C'
SELECT DECODE(3,1,'A',2,'B') res FROM dual;       -- res: NULL
SELECT DECODE(NULL,1,'A',2,'B') res FROM dual;    -- res: NULL
SELECT DECODE(NULL,1,'A',NULL,'B') res FROM dual; -- res: 'B'

DECODE function is very powerful and convenient to use, but it is Oracle dialect, we still want to use as little as possible. In fact, any function that DECODE function can achieve can be realized by CASE function or IF statement of SQL, but it is relatively tedious to write.

CASE function
Syntax:

CASE [ expression ]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  WHEN condition_n THEN result_n
  ELSE res
END

Examples (to facilitate comparison between examples and save space, avoid too long and inconvenient to read, the following four examples are abbreviated into one line, in actual development there are generally broken lines better to read):

SELECT CASE 1 WHEN 1 THEN 'A' WHEN 2 THEN 'B' ELSE 'C' END res FROM DUAL; -- res: 'A'
SELECT CASE 2 WHEN 1 THEN 'A' WHEN 2 THEN 'B' ELSE 'C' END res FROM DUAL; -- res: 'B'

SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END res FROM DUAL; -- res: 'B'
SELECT CASE WHEN NULL IS NULL THEN 'NULL' ELSE 'NOT NULL' END res FROM DUAL; -- res: 'NULL'

In order to deal with NULL more flexibly, Oracle provides NVL, NVL2, NULLIF, COALESCE and other functions to deal with NULL. Let's look at an example of these four functions:

NVL function
Syntax: NVL(expr1, expr2)
Description: Usually used for a value that may be NULL, but there is no need to convert null values in the query results. If the first parameter is NULL, the second parameter is returned, otherwise the first parameter is returned.
Example:

SELECT NVL(NULL,'b') res FROM DUAL; -- res: b
SELECT NVL('a','b') res FROM DUAL;  -- res: a

SELECT NVL(NULL,2) res FROM DUAL; -- res: 2
SELECT NVL(1,2) res FROM DUAL;    -- res: 1

NVL2 function
Syntax: NVL2(expr1, expr2, expr3)
Description: Commonly used for requirements that require conversion regardless of whether a value is NULL or not. If the first parameter is NULL, the third parameter is returned, otherwise the second parameter is returned.
Example:

SELECT NVL2(NULL,'b','c') res FROM DUAL; -- res: c
SELECT NVL2('a','b','c') res FROM DUAL;  -- res: b

SELECT NVL2(NULL,2,3) res FROM DUAL; -- res: 3
SELECT NVL2(1,2,3) res FROM DUAL;    -- res: 2

NULLIF function
Grammar: NULLIF(expr1, expr2)
Description: If the first parameter is equal to the second parameter, NULL is returned, otherwise the first parameter is returned. This function requires the first parameter to be compatible with the second parameter type, and the first parameter can not be NULL, which has many limitations, and can be replaced by CASE function or DECODE function, so it is seldom used in actual development.
Example:

SELECT NULLIF('a','a') res FROM DUAL; -- res: NULL
SELECT NULLIF('a','b') res FROM DUAL; -- res: a

SELECT NULLIF(1,1) res FROM DUAL; -- res: NULL
SELECT NULLIF(1,2) res FROM DUAL; -- res: 1

COALESCE function
Syntax: COALESCE (expr1, expr2,..., exprn)
Description: This function accepts two or more parameters, finds the first non-NULL parameter from left to right and returns it. If all parameters are NULL, it returns NULL. This function requires the data type of parameter list to be compatible, and has the function of short-circuit calculation. After finding the non-NULL parameters, it will not continue.
Case study:

SELECT COALESCE(NULL,NULL,'a') res FROM DUAL;  -- res: a
SELECT COALESCE('','','a') res FROM DUAL;      -- res: a
SELECT COALESCE(NULL,0,1,2) res FROM DUAL;     -- res: 0
SELECT COALESCE(NULL,NULL,NULL) res FROM DUAL; -- res: NULL

3.3, NULL and Index

As we all know, index is a storage structure created in commercial databases to accelerate the retrieval of data rows in tables. There are many kinds of indexes in Oracle, among which the basic single column index and composite index do not store NULL rows with all index columns. Proving examples:

-- First give demo.t_staff.hire_date Column creates an index
CREATE INDEX idx_hire_date ON demo.t_staff(hire_date);

-- Then query the number of rows in the index because hire_date All columns are NULL，The result is 0
SELECT t.table_owner,t.table_name,t.index_name,t.num_rows FROM SYS.USER_INDEXES t WHERE t.index_name='IDX_HIRE_DATE';

-- Women Employees hire_date All set to 2016-06-01
UPDATE demo.t_staff t SET t.hire_date=TO_DATE('2016-06-01','yyyy-mm-dd') WHERE t.gender=0;
COMMIT;

-- reconstruction IDX_HIRE_DATE Indexes
ALTER INDEX demo.idx_hire_date REBUILD;

-- Then query the number of rows in the index, and the result is 5
SELECT t.table_owner,t.table_name,t.index_name,t.num_rows FROM SYS.USER_INDEXES t WHERE t.index_name='IDX_HIRE_DATE';

Here's a typical case of using NULL and function-based indexes to optimize queries: if the personnel department wants a function that facilitates the assignment of mentors to new employees. A little analysis of the demand will show that most of the employees in the company are full-time employees, and only a small part of the probationary employees. Personnel commissioners often inquire about the few new employees in the actual use process, while formal employees seldom inquire about them. At this time, only the rows belonging to new employees can be indexed. On the one hand, it saves the cost of maintaining the index, reduces the storage space of the index, and on the other hand, the index with fewer rows can be retrieved faster. Example:

-- Add a field to the employee table to record the trial status prob_status
ALTER TABLE demo.t_staff ADD(prob_status NUMBER(1));
COMMENT ON COLUMN demo.t_staff.prob_status IS 'trial status｛0 Has been corrected/1 Probationary｝';

-- According to the conclusion of the above proof, the employee's status in the trial is changed to NULL Well, naturally you won't be able to access the index.
CREATE INDEX idx_prob_status ON demo.t_staff(DECODE(prob_status,1,1,NULL));

-- When you query the probationary employees, you will go to the index. You can confirm the number of index rows and analyze the execution plan of the next sentence by yourselves.
SELECT * FROM demo.t_staff t WHERE DECODE(t.prob_status,1,1,NULL)=1;

3.4, NULL and SQL

When DDL meets NULL: Net Programmers Learn Oracle Series (6): Tables, Fields, Annotations, Constraints, Indexes The basic DDL grammar has been introduced and is relatively simple. This section will not go into detail. However, there are many details related to NULL about DDL statements that operate on non-null constraints and default values. Five experimental results will be given directly here. Interested readers can verify them by themselves.

• When creating tables, NULL/NOT NULL must be placed behind DEFAULT if both non-null constraints and default values are set for fields.
• When a field is created, if the default values are set at the same time, the column of the existing row in the table will automatically be updated to the default values, otherwise all will be NULL.
• When modifying table fields, NULL/NOT NULL must be followed by DEFAULT if both non-null constraints and default values are modified at the same time.
• Adding or modifying default values does not affect the values of existing rows in the table.
• When adding or modifying non-null constraints, if the field was originally NOT NULL constraints, the NULL constraints can be changed directly regardless of whether there is data in the table. If the field is originally NULL constrained and the column does not contain NULL values, it can still be modified to NOT NULL constraints; if the column already has NULL values, it can not be modified to NOT NULL constraints.

When sub-queries encounter NULL: Some sub-query result sets contain NULL, it may lead to "result set exception" of external queries, such as non-related sub-queries in IN/NOT IN.

IN non-related sub-query example:

WITH
t1 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL),
t2 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL)
SELECT cid,cname FROM t1 WHERE t1.cname IN(SELECT t2.cname FROM t2);

Result:

       CID CNAME
---------- -----
         1 Trump

NOT IN non-related sub-query example:

WITH
t1 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL),
t2 AS(SELECT 1 cid,'Hillary' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL)
SELECT cid,cname FROM t1 WHERE t1.cname NOT IN(SELECT t2.cname FROM t2);

Result:

       CID CNAME
---------- -----

Because NULL is neither equal to any value nor to any value, the result set of the above two single-column unrelated sub-queries is one row less than expected. The rule of multi-column sub-query is the same, and the analysis method is: NULL!= Any value & NULL!= Any value. Here are a few more examples of multi-column sub-queries. Even if you still don't understand them, I believe you can understand the mystery of them by comparing these sentences and results according to the analysis method I gave you.

Example:

WITH
t1 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL),
t2 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL)
SELECT cid,cname FROM t1 WHERE (t1.cid,t1.cname) IN(SELECT t2.cid,t2.cname FROM t2);

Result:

       CID CNAME
---------- -----
         1 Trump

Example:

WITH
t1 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL),
t2 AS(SELECT 1 cid,'Hillary' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL)
SELECT cid,cname FROM t1 WHERE (t1.cid,t1.cname) NOT IN(SELECT t2.cid,t2.cname FROM t2);

Result:

       CID CNAME
---------- -----
         1 Trump

Example:

WITH
t1 AS(SELECT 1 cid,'Trump' cname FROM DUAL UNION ALL SELECT 2,NULL FROM DUAL),
t2 AS(SELECT 1 cid,'Hillary' cname FROM DUAL UNION ALL SELECT 3,NULL FROM DUAL)
SELECT cid,cname FROM t1 WHERE (t1.cid,t1.cname) NOT IN(SELECT t2.cid,t2.cname FROM t2);

Result:

       CID CNAME
---------- -----
         1 Trump
         2 

If you still don't understand the results after looking at the three examples above, then look at the conditional analysis of the following four sentences and see the three examples above. Readers who have understood it can skip this section!

-- The sentence WHERE The condition is equivalent to t.dept_code = '010101'
SELECT COUNT(1) res FROM demo.t_staff t WHERE t.dept_code IN('010101'); -- res: 5

-- The sentence WHERE The condition is equivalent to t.dept_code = '010101' OR t.dept_code = NULL
SELECT COUNT(1) res FROM demo.t_staff t WHERE t.dept_code IN('010101',NULL); -- res: 5

-- The sentence WHERE The condition is equivalent to t.dept_code != '010101'
SELECT COUNT(1) res FROM demo.t_staff t WHERE t.dept_code NOT IN('010101'); -- res: 11

-- The sentence WHERE The condition is equivalent to t.dept_code != '010101' AND t.dept_code != NULL
SELECT COUNT(1) res FROM demo.t_staff t WHERE t.dept_code NOT IN('010101',NULL); -- res: 0

When set queries and grouped queries encounter NULL: Net Programmers Learn Oracle Series (14): Subquery, Collection Query In Section 2, it is mentioned that the processing results of three operators, INTERSECT, UNION and MINUS, do not contain duplicate rows. If a column contains NULL, other columns are considered duplicate rows if they are the same as other rows. Example:

SELECT 1 cid,'Trump' cname FROM DUAL
UNION
SELECT 2 cid,NULL cname FROM DUAL
UNION
SELECT 2 cid,NULL cname FROM DUAL;

Result:

       CID CNAME
---------- -----
         1 Trump
         2 

NULL and NULL are also considered equal in grouped queries. Example:

WITH t AS(
  SELECT 1 cid,'Trump' cname FROM DUAL
  UNION ALL
  SELECT 2 cid,NULL cname FROM DUAL
  UNION ALL
  SELECT 2 cid,NULL cname FROM DUAL
)
SELECT t.cname,COUNT(1) cnt FROM t GROUP BY t.cname ORDER BY cnt;

Result:

CNAME        CNT
----- ----------
Trump          1
               2

When sorting encounters NULL: The sorting clause ORDER BY defaults to ascending order (ASC), the rows with NULL ranking column value rank last, and if DESC is specified, NULL ranks first. You can remember this rule in this way - NULL is the largest in sorting. Of course, the ordering rules for the specified NULL can be displayed in Oracle. NULL is required to be at the top of ascending order, NULLS FIRST can be specified, NULL at the bottom of descending order and NULLS LAST can be specified. Example:

SELECT 1 cid,'Trump' cname FROM DUAL
UNION ALL
SELECT 2,'Hillary' FROM DUAL
UNION ALL
SELECT 3,NULL FROM DUAL
ORDER BY 2 NULLS FIRST;

Result:

       CID CNAME
---------- -------
         3 
         2 Hillary
         1 Trump

When UPDATE meets NULL: Net Programmers Learn Oracle Series (12): Addition, deletion and modification As detailed, this section will not be repeated.

4, summary

This paper mainly describes three common and very special objects in Oracle. Among them, DUAL and ROWID are relatively simple to use; NULL is different, not only its own special, many common statements, operators, functions and so on also become special, the details are too many, limited to space and my energy, this article only introduces some relatively common situations, more subtleties to be found in learning and work.