SQL judge null Oracle, SQL server

SQL server

Replace null: isnull (arg, value)

For example, select isnull (price, 0.0) from orders. If the price is null, replace it with 0.0.

Comparison with null: is not null, is null

For example, select * from orders where price is null and price is null.

For example, select * from orders where price is not null, and price is not equal to null

Oracle

Replace null: nvl (arg, value)

For example, select nvl (price, 0.0) form orders

Comparison with null: is not null, is null

For example, select * from orders where price is null and price is null.

For example, select * from orders where price is not null, and price is not equal to null

Oracle

This is a frequently asked question. Especially when the customer used oracle before, he had a question when using SQL server, that is, SQL server and oracle behave differently in processing null values.

In oracle, null values are considered to be infinite values. Therefore, if they are listed in ascending order, they are listed at the end.

In SQL server, the opposite is true. null values are considered to be an infinitely small value. Therefore, if they are sorted in ascending order, they are ranked first.

For example

Select [id]

From [demo]. [dbo]. [orders] order by id

The following results are displayed:

Is there any way to make the default mechanism of SQL server the same as that of oracle? The answer is: no

But we can try some workarounds, such as writing code like below.

Select [id]

From [demo]. [dbo]. [orders] order by case when id is null then 1 else 0 end

In this way, you can see the following results

If this column has an index, you can see the following execution plan