SQL Judge null Oracle,sql server

SQL Server

Replace Null:isnull (Arg,value)

For example: Select IsNull (price,0.0) from orders, if Price is null, replace with 0.0

Compared to null: is not null,is null

For example, select * from to orders where price is null and price equals NULL

such as: SELECT * from Orders where, "not NULL", price does not equal null

Oracle

Replace NULL:NVL (Arg,value)

such as: Select NVL (price,0.0) Form orders

Compared to null: is not null,is null

For example, select * from to orders where price is null and price equals NULL

such as: SELECT * from Orders where, "not NULL", price does not equal null

Oracle

This is a question that is often asked. In particular, when using Oracle, the client would have a problem with SQL Server, which is that SQL Server behaves differently from Oracle on top of NULL values.

In Oracle, a null value is considered to be an infinity value, so if you arrange it in ascending order, it will be ranked at the very end.

In SQL Server, in contrast, a null value is considered to be an infinitesimal value, so if you arrange it in ascending order, it will be in the front.

Such as

SELECT [ID]

from [Demo]. [dbo]. [Orders] ORDER by ID

You will see the following effect

So is there any way to make this default mechanism for SQL Server like Oracle? The answer is: no

But we can think of some workarounds, such as writing code like the following

SELECT [ID]

from [Demo]. [dbo]. [Orders] order from case when ID was null then 1 else 0 end

In this case, you can see the following effect

If the column has a CREATE index, you can see the following execution plan