SQL における Null についての深い理解

NULL は、コンピューターやプログラミングの世界では未知、不確かであることを意味します。漢訳では「空」ですが、この空（ヌル）はそれほど空（空）ではありません。 Null は未知の状態、つまり将来の状態を表します。たとえば、Xiao Ming がポケットにいくら持っているかはわかりませんが、それが 0 であるかどうかはわかりません。現時点では、Null はコンピュータで表すために使用されます。未知で不確実。

SQL に精通している人は Null について何の疑問も持たないでしょうが、それを包括的にまとめた記事を見つけるのはまだ困難です。英語版を見つけたので良い感じです。

Tony Hoare は 1965 年に null 参照を発明し、それが自分の犯した「10 億ドルの間違い」であると考えましたが、50 年後の現在でも、SQL の null 値は多くの一般的なエラーの原因となっています。 最も衝撃的な状況をいくつか見てみましょう。

Null はサイズ/等価性の判断をサポートしません

次の 2 つのクエリは、users テーブルにレコードがいくつあっても、0 行を返します:

select * from users where deleted_at = null;

– result: 0 rows

select * from users where deleted_at != null;

– result: 0 rows

これは、null が「不明な」型を表すためです。つまり、通常の条件演算子を使用して null を他の値と比較することは意味がありません。 Null は Null と等しくありません (おおよその理解: 未知の値が未知の値と等しくなることはあり得ず、この 2 つの値の間の関係も不明です。そうでないと数学と論理が台無しになります)。

– 注: 次の SQL は MySQL に適しています。Oracle の場合は、dual;

select null > 0;

– result: null

select null < 0;

– result: null

select null = 0;

– result: null

select null = null;

– result: null

select null != null;

– result: null

から…を追加する必要があります。 値を null と比較する正しい方法は、is キーワードと is not 演算子を使用することです:

select * from users

where deleted_at is null;

– result: 所有被标记为删除的 users

select * from users

where deleted_at is not null;

– result: 所有被标记为删除的 users

2 つの列の値が異なるかどうかを判断したい場合は、次の関数を使用できます:

select * from users

where has_address is distinct from has_photo

– result: 地址(address)或照片(photo)两者只有其一的用户

Nullとは関係ありません

サブクエリ (部分選択) は、データをフィルタリングする非常に便利な方法です。たとえば、パッケージを持たないユーザーにクエリを実行する場合は、次のクエリを作成できます:

select * from users 

where id not in (select user_id from packages)

しかしこの時点で、packages テーブル内の行の user_id が null の場合、問題が発生します。返される結果は空です。なぜこの奇妙なことが起こるのかを理解するには、SQL コンパイラーが何をしたかを理解する必要があります。以下はより単純です。例:

りー

この SQL ステートメントは次のように変換されます:

select * from users 

where id not in (1, 2, null)

id != null の結果は不明な値である null であることがわかっており、任意の値と null の間の AND 演算の結果は null であるため、この結果は他のどの条件とも同等ではありません。 null の値は true ではありません。

条件が逆の場合、クエリ結果に問題はありません。 ここで、パッケージを使用してユーザーにクエリを実行します。

select * from users 

where id != 1 and id != 2 and id != null

同様に、簡単な例を使用できます:

select * from users 

where id in (select user_id from packages)

この SQL は次のように変換されます:

select * from users

where id in (1, 2, null)

where 句は一連の or 条件であるため、そのうちの 1 つが null になるかどうかは問題ではありません。 true 以外の値は節の他の部分の計算結果に影響を与えず、無視されるのと同等です。

Null と並べ替え

並べ替えの際、Null 値が最大であるとみなされ、降順 (降順) で並べ替える場合、Null 値が最初にランク付けされるため、問題が発生する可能性があります。

次のクエリはスコアに基づいてユーザーのランキングを表示するものですが、スコアのないユーザーが上位にランク付けされます

select * from users 

where id = 1 or id = 2 or id = null

! この種の問題を解決するには 2 つの考え方があります。最も簡単な方法は、Coalesce を使用して null の影響を排除することです:

select name, points

from users

order by 2 desc;

– points 为 null 的记录排在所有记录之前!

データベースのサポートを必要とする別の方法として、並べ替え時に null 値を最初に置くか最後に置くかを指定する方法があります:

– 在输出时将 null 转换为 0 :

select name, coalesce(points, 0)

from users

order by 2 desc;

– 输出时保留 null, 但排序时转换为 0 :

select name, points

from users

order by coalesce(points, 0) desc;

もちろん、null は、除数がゼロの場合の数学的演算エラーの処理など、エラーの発生を防ぐためにも使用できます。

0で割る

ゼロで割るのは非常に痛ましいエラーです。昨日は正常に動作していた SQL が、0 で割ると突然異常が発生しました。一般的な解決策は、最初に case ステートメントを使用して分母が 0 かどうかを判断し、次に除算演算を実行することです。

りー

ase ステートメントのやり方は実際には醜く、分母が再利用されています。単純な状況であれば問題ありませんが、分母が非常に複雑な式の場合は悲劇が起こります。読みにくく、保守や変更が難しく、注意しないと多くのバグが発生することになります。 .

この時点で、nullif を使用して、分母が 0 の場合に null になるようにすることができます。これにより、num_users = 0 の場合、返される結果は null になります。

null は望まないが、0 または他の数値に変換したい場合は、前の SQL に基づいて coalesce 関数を使用できます:

select name, coalesce(points, 0)

from users

order by 2 desc nulls last;

結論

トニー・ホアは自分の間違いを後悔しているかもしれませんが、少なくともヌルの問題は簡単に解決できるので、新しい究極のスキルを練習して、ヌルによって掘られた無効な穴 (無効化) から離れてください!。

以上がSQL における Null についての深い理解の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。