Null and dual

1. Usage of null

Some people often ask, what is null? As the name suggests, null is null. in Oracle and other databases, the column length of a table containing null values is zero. Oracle allows blank fields of any data type, except for the following two cases:

1. Define this column as the primary key field (primary key );

2. When defining this column, it is explicitly added with the not null restriction field.

1. Details:

1. It is equivalent to no value and is unknown;

2. null and 0, empty strings, and spaces are different;

3. add, subtract, multiply, and divide null values. The result is still null;

4. The nvl function is used for null processing;

5. keyword "is null" and "is not null" are used for query and comparison ";

6. null values cannot be indexed. Therefore, some data that meets the query conditions may not be found. For example, in count (*), nvl (column name, 0) is used for processing and re-query;

7. Sorting is larger than other data (the index is sorted in descending order by default, small → large), so the null value is always at the end.

 

. Example:

SQL> select 1 from dual where null = NULL;

No records found

SQL> select 1 from dual where null = '';

No records found

SQL> select 1 from dual where ''= '';

No records found

SQL> select 1 from dual where NULL is null;

1

---------

1

SQL> select 1 from dual where nvl (null, 0) = nvl (null, 0 );

1

---------

1

-- Add, subtract, multiply, and divide null values. The result is still null.

SQL> select 1 + null from dual;

SQL> select 1-null from dual;

SQL> select 1 * null from dual;

SQL> select 1/null from dual;

A record is found.

. Set certain columns as null values

Update Table1 set col1 = NULL where col1 is not null;

Familiar with Oracle null usage and its conventions to ensure that the result is OK.

2. Dual pseudo Column

Description:

Dual is an existing table in Oracle that can be read by any user. It is often used in select statement blocks without a target table.

2. 1. Usage:

-- View the current connected user

SQL> Select User from dual;

User

------------------------------

System

-- View the current date and time

SQL> select sysdate from dual;

Sysdate

----------

18-4-03

SQL> select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual;

To_char (sysdate, 'yy

-------------------

22:37:56

-- Used as a calculator

SQL> select 1 + 2 from dual;

1 + 2

----------

3

-- View the Sequence Value

SQL> Create sequence AAA increment by 1 start with 1;

SQL> select AAA. nextval from dual;

Nextval

----------

1

SQL> select AAA. currval from dual;

 

Currval

----------

1