Non-equal sign in Oracle

Today, I encountered a problem where Oracle is not equal. I finally searched and found the following information to share with you the non-equal number in Oracle: Then I used instr (na

Today, I encountered a problem where Oracle is not equal. I finally searched and found the following information to share with you the non-equal number in Oracle: Then I used instr (na

Today, I encountered a problem where Oracle is not equal. I finally searched and found the following information to share with you about Oracle's non-equal sign:

In Oracle,

<>
! =
~ =
^ =

They all mean no. Can be used.

But it's strange. When I want to take out products whose price is not 180000: (price is Number type)

SELECT id, name FROM product where price <> 180000;

When this statement is executed, the priceis null record is unavailable. That is, goods whose price is null cannot be obtained. Required:

SELECT id, name FROM product where price <> 180000 or price is null.

String fields have the same problem.

Remember: null can only be determined by is null or is not null. Other operators and null operations are both false.

Test: select * from test where name <> 'xn '. Only records with non-null name can be found. Remove name <> 'xn. There is a problem with this writing method.

Instr (name, 'xn ') = 0 is used to determine whether the value is valid if the name is not null. If the name is empty, this judgment goes wrong again. You have to use instr (concat (name, 'xx'), 'xn ') = 0 to judge the cause. Even if the name is null, when it is connected to 'XX, it will not be empty.

Therefore, the final SQL statement is:

Select * from test where instr (concat (name, 'xx'), 'xn ') = 0 to query records whose name field is not equal to 'xn.

Alternatively, you can use select * from test where nvl (name, 'xx') <> 'xn 'to query records whose name field is not equal to 'xn.