Oracle parent_id = NULL

There is a table

Node

ID	No.
Parent_id	Parent node
Name	Name

Self-join. If no parent node exists, the value of parent_id is null, instead of 0, because no node numbered 0 exists.

Select * from Node

Start with parent_id is null

Connect by prior id = parent_id

All nodes can be identified by hierarchy.

Now, if you want to query the subnode from 1, the query is like

Select * from Node

Start with parent_id = 1

Connect by prior id = parent_id

Consider providing parameterized queries. Here, 1 and null are variables.

Select * from Node

Start with parent_id =: parent_id

Connect by prior id = parent_id

If the input parameter is null, no record can be found. The reason is that null cannot be compared in the Oracle world view, because null <> null.

Can I solve the problem by using in? Because in is actually a traversal and comparison of each hash value in (,), and the effect is the same as that in =. Writing in (null) cannot find a record.

After learning about null, I found that Null-<n> = NULL, that is, null minus any number is null. Therefore, the following method is obtained:

Select * from Node

Start with nvl (parent_id-: parent_id/2, 0) =: parent_id/2

Connect by prior id = parent_id

When 0 is provided as a parameter, it is equivalent to null.

This method focuses on expressions.Start with nvl (parent_id-: parent_id/2, 0) =: parent_id/2

If the value of a given parameter is 0, the value on the right is 0, and the value on the left is required to be 0, while the value on the nvl (x, 0) = 0 can only be null and 0.

Considering that X isParent_id-0Therefore, the equation is true only when parent_id is null.

If the given parameter is a non-zero number N, the premise of equality is nvl (x, 0) = <n/2>. At this time, if X is null, the equation cannot be true.

That is, the equation is true only when parent_id-parent_id/2 = parent/2.