NULL compare operator <=>
MySQL
SELECT 99 <=> NULL, NULL <=> NULL;
+-------------+---------------+
| 99 <=> NULL | NULL <=> NULL |
+-------------+---------------+
| 0 | 1 |
+-------------+---------------+
IS NULL
IS NOT NULL
PostgreSQL
针对不同的类型，需要创建不同的函数 和 <=>
create or replace function nulleq(int,int) returns int as
$$
declare
begin
if $1 is null and $2 is null then
return 1;
else
return 0;
end if;
end;
$$
language plpgsql;
postgres=# create operator <=> (procedure=nulleq,leftarg=int,rightarg=int);
CREATE OPERATOR
postgres=# select 1 <=> null;
?column?
----------
0
(1 row)
postgres=# select null <=> null;
?column?
----------
1
(1 row)
IS NULL
IS NOT NULL
coalesce
MySQL
coalescePostgreSQL
postgres=# select coalesce(null,1,2);
coalesce
----------
1
(1 row)
postgres=# select coalesce(null,null,2);
coalesce
----------
2
(1 row)
postgres=# select coalesce('a',null,'b');
coalesce
----------
a
(1 row)order
MySQL
SELECT col1 FROM tab ORDER BY ISNULL(col1), col1; -- null is first
SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC; -- Descending order, with NULLs first
All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.PostgreSQL
默认nulls比其他值更大
postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test values (1),(2),(3),(null),(null);
INSERT 0 5
postgres=# select * from test order by test;
id
----
1
2
3
(5 rows)
postgres=# select * from test order by id nulls first;
id
----
1
2
3
(5 rows)
postgres=# select * from test order by id nulls last;
id
----
1
2
3
(5 rows)
postgres=# select * from test order by id desc;
id
----
3
2
1
(5 rows)
postgres=# select * from test order by id desc nulls first;
id
----
3
2
1
(5 rows)
postgres=# select * from test order by id desc nulls last;
id
----
3
2
1
(5 rows)ISNULL, NULLIF, IFNULL
MySQL
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
SELECT IFNULL(1,0);
+-------------+
| IFNULL(1,0) |
+-------------+
| 1 |
+-------------+
SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
| 10 |
+-----------------+
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL |
+-------------+
SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
| 1 |
+-------------+
ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+
ISNULL(expr)
If expr is NULL, ISNULL() returns 1, otherwise it returns 0.
SELECT ISNULL(1+1);
+-------------+
| ISNULL(1+1) |
+-------------+
| 0 |
+-------------+
SELECT ISNULL(1/0);
+-------------+
| ISNULL(1/0) |
+-------------+
| 1 |
+-------------+PostgreSQL
postgres=# create or replace function ifnull(int,int) returns int as
$$
select case when $1 is not null then $1 else $2 end;
$$
language sql;
CREATE FUNCTION
postgres=# select ifnull(null,2);
ifnull
--------
2
(1 row)
postgres=# select ifnull(1,3);
ifnull
--------
1
(1 row)
nullif
postgres=# select nullif(1,1);
nullif
--------
(1 row)
postgres=# select nullif(1,2);
nullif
--------
1
(1 row)
isnull
postgres=# create or replace function isnull(anyelement) returns int as
$$
select case when $1 is null then 1 else 0 end;
$$
language sql;
CREATE FUNCTION
ostgres=# create table ttt(id int);
CREATE TABLE
postgres=# insert into ttt values (null);
INSERT 0 1
postgres=# insert into ttt values (1);
INSERT 0 1
postgres=# select isnull(id),id from ttt;
isnull | id
--------+----
1 |
0 | 1
(2 rows)