Null and dual

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.