NULLール〜SQLの基本とNULLについて〜
ふっちーです。
SQLのブログは初投稿です。
Oracle MASTER Bronze 12c SQLの資格取得を目指して勉強しております。
今回は基礎の部分とNULLを中心に取り上げていきます。
目次
・用語
・NULLについて
・記述の順番
テーブル
テーブル名：cope
| number | name | Salary | perpay | |--------|------|--------|--------| | 1 |佐々木| 180000 | (null) | | 2 | 富田 | 200000 | (null) | | 3 | 桑原 | 730000 | (null) | | 4 | 衛藤 | 360000 | 70000 | | 5 | 田中 | 310000 | 40000 | | 6 | 林 | 270000 | (null) | | 7 | 高木 | 190000 | 50000 | | 8 | 藤原 | 450000 | 90000 |
以下のSQL文ではこのテーブルを使用しています。
用語
select文の機能とSQL文の分類
| 機能名 | 役割 | 具体例 |
|---|---|---|
| 射影 | 特定の列を取り出す | select |
| 選択 | 特定の行を取り出す | where, having等 |
| 結合 | 表と表を横に繋げる | join等 |
SQLと言ったら外せないselect文についてです。
個人的な経験ですが、試験において意外とつまづきやすいのがこのような名称の問題だと思っています。
他にもSQL文の分類である
・DML(Data Manipulation Language)
・DDL(Data Definition Language)
・DCL(Data Control Language)
・トランザクション制御
SQLにはselect文の様なデータ表を表示する機能だけでなく、内容を書き換えたり、データに接続できる権限の制御まで行えます。
上の4つはその種類分けにつけられている名称です。
select文はDMLに当てはまります。
関数のタイプ
SQLには様々な関数があります。
これはSQLには数値や日付、文字データなどのデータが使用可能です。
SQLの関数には2種類あります。
・単一行関数
・グループ関数
単一行関数
単一行関数は表に対して、一行の結果を出力します。
引数には数値だけではなく、列名や式を入力できるものもあります。
いくつか例を挙げてみましょう。
trunc関数
trunc関数は数値の切り捨てを行います。
select trunc(m, n) from cope;
mを小数点n桁以下を切り捨てします。
nは任意で入力でき、入力しない場合は小数点以下を切り捨てします。
-(マイナス)で入力した場合は小数点の左側を数える。
concat関数
concat関数は文字列の結合を行います。
select concat('m', 'n') as test from cope;
| TEST | |------| | mn |
2つまでしか結合できないので、3つ以上結合したい場合は(||)を使いましょう。
グループ関数
グループ関数は列に対して、一行の結果を出力します。
こちらもいくつか例を見てみましょう。
avg関数
指定した列の平均値を求めます。
そのため、数値のみの行にしか適用できません。
select avg("Salary") as test from cope;
| TEST | |--------| | 336250 |
count関数
指定した列内のNULL以外の行数を出力します。
select count("perpay") as test from cope;
| TEST | |------| | 4 |
ただし、引数に*(アスタリスク)を入れるとNULLを含めて数えます。
select count(*) as test from cope;
| TEST | |------| | 8 |
max関数
指定した列内の最大値を出力します。
select max("Salary") as test from cope;
| TEST | |--------| | 730000 |
ここでいう最大値は数値だけではなく、日付や文字列も含めます。
日付の場合は一番新しい日付が出力されます。
文字列の場合は文字コードが最も大きい文字列が出力されます。
NULLについて
SQLにおけるNULLがややこしかったのでここでまとめてみたいと思います。
四則演算子
NULLは四則演算(+, -, *, /)のどれを適用してもNULLを返します。
select 100*NULL from cope;
| 100*NULL | |----------| | (null) |
NULLを0として扱いたい場合はNVL関数が使えます。
NVL関数については後述しています。
比較演算子
比較を行った場合、基本的にTrueかFalseが返ってくるのが普通ですが、ここでもNULLはNULLを返します。
select * from cope where "perpay" = NULL;
上記も四則演算同様にエラーは発生しませんが、該当なしと出力されます。
しかし、比較自体が行えないわけではありません。
where句でisを使うとNULLかどうかを判断することができます。
select * from cope where "perpay" is NULL;
| number | name | Salary | perpay | |--------|------|--------|--------| | 1 |佐々木| 180000 | (null) | | 2 | 富田 | 200000 | (null) | | 3 | 桑原 | 730000 | (null) | | 6 | 林 | 270000 | (null) |
ORDER BYでNULLはどこに来る？
order byは表のソートを行う機能です。
select * from table order by 列名;
この時列にNULLが含まれている場合どうなるでしょうか。
まず、昇順で並べてみましょう。
select "perpay" from cope order by "perpay";
| perpay | |--------| | 40000 | | 50000 | | 70000 | | 90000 | | (null) | | (null) | | (null) | | (null) |
次に、降順で並べてみましょう。
select "perpay" from cope order by "perpay" desc;
| perpay | |--------| | (null) | | (null) | | (null) | | (null) | | 90000 | | 70000 | | 50000 | | 40000 |
このように、ソートでは１番大きいものとして扱われます。
NULLを使った関数
前述した汎用関数はNULLを使用できます。
NVL関数
select nvl("perpay", 0) as test from cope
| TEST | |-------| | 0 | | 0 | | 0 | | 70000 | | 40000 | | 0 | | 50000 | | 90000 |
式がNULLの場合、設定された値を戻します。
例の場合はNULLの時0を返します。
NVL2関数
select nvl2("perpay", 1, 0) as test from cope
| TEST | |------| | 0 | | 0 | | 0 | | 1 | | 1 | | 0 | | 1 | | 1 |
式がNULLでないなら1つ目の値を、NULLなら2つ目の値を戻します。 例の場合はNULL以外で1、NULLで0を出力します。
NULLIF関数
select nullif("Salary", 180000) as test from cope
| TEST | |--------| | (null) | | 200000 | | 730000 | | 360000 | | 310000 | | 270000 | | 190000 | | 450000 |
2つの式を比較し、等しい場合はNULLを返す。異なる場合は1つ目の式を戻します。
例の場合はSalaryが180000のときNULLを返します。
まとめ
今回はSQLの用語とNULLについてまとめました。
特にNULLは先月まで勉強していたPythonでは見られない動作をするので、なんとかして慣れたいです。
また、個人的に今一番の壁が相関副問合せなので、これも克服していきたいです。
