SQLのSELECT文の基礎を学ぼう
データベース操作において非常に重要な役割を果たすSQL（Structured Query Language）の中でも特に基礎となるSELECT文について学んでいきましょう。SELECT文はデータベースからデータを抽出するためのクエリであり、情報を取得するための強力なツールです。この記事では、SELECT文の構文や基本的な使い方を解説していきます。
基本的なSELECT文の構文は以下の通りです。
SELECT 列名1, 列名2, ...
FROM テーブル名
WHERE 条件本記事はMySQLをベースに書いているので他のデータベースでは書き方が違うものもありますのでご注意ください。
色々なパターンのSELECTを見られるように、「問題・解決・解説」の形式で書いていきたいと思います。
テーブルからすべての行と列を取得する
問題
あるテーブル（表）のすべてのデータを表示したい。
解決策
特殊文字「*」を使い、テーブルに対してSELECTを実行します。
select * from emp;解説
SQLでは、「*」文字には特別な意味があります。この文字を使うと、指定したテーブルのすべての列を返します。WHERE句を指定していないので、すべての行を返します。
代わりに、次のようにそれぞれの列を個々に指定することもできます。
select
empno,ename,job,sal,mgr,hiredate,comm,deptno
from emp;対話的に実行するアドホックなクエリでは、SELECT *を使う方が簡単です。
しかし、プログラムコードを記述する際には、列を個々に指定する方が優れています。
パフォーマンスは同じですが、明示的に指定すると、そのクエリが返す列が常にわかります。同様に、このようなクエリの方が第三者にも理解しやすくなります。SELECT *の問題は、クエリがコード内にあり、そのプログラムがクエリから予想とは異なる列を取得する場合にも生じます。少なくとも、すべての列を指定してその列の中の1つ以上がない場合には、発生したエラーの原因が特定の欠損列である可能性が高いでしょう。
テーブルから行の一部を取得する
問題
あるテーブルの特定の条件を満たす行だけを表示したい。
解決策
WHERE句を使って取得する行を指定します。例えば、部署番号10に割り当てられたすべての従業員を表示するには次のようにします。
select * from emp where deptno = 10;解説
WHERE句を使うと、興味のある行だけを取得できます。WHERE句の式が真となるすべての行を返します。
ほとんどのDBでは、=、<、>、<=、>=、!=、<>などの一般的な演算子をサポートしています。
また、複数の条件を満たす行を取得したい場合もあるでしょう。
これは、AND、OR、丸括弧を指定すると実行できます。
複数の条件を満たす行を取得する
問題
複数の条件を満たす行を返したい。
解決策
WHERE句をOR句やAND句と共に使います。例えば、部署10の全従業員、歩合給を受け取っている全従業員、そして給与が2,000ドル以下の部署20の全従業員を同時に特定したい場合は次のようにします。
select *
from emp
where deptno = 10
or comm is not null
or sal <= 2000<br>and deptno=20;解説
AND、OR、丸括弧を組み合わせて使うと、複数の条件を満たす行を返すことができます。この例では、WHERE句で次を満たす行を探します。
- DEPTNO（部署番号）が10、または
- COMM（歩合給）がNULLでない、または
- DEPTNOが20で給与が2,000ドル以下の全従業員
丸括弧を付けると、その括弧内の条件は一緒に評価されます。
例えば、次のようにこのクエリを丸括弧を使って書いたら結果セットがどのように変わるか実行してみてください。
select * from emp
where (deptno = 10
or comm is not null
or sal <= 2000
)
and deptno=20;テーブルから列の一部を取得する
問題
あるテーブルのすべての列ではなく特定の列の値を表示したい。
解決策
対象となる列を指定します。例えば、従業員の名前、部署番号、給与だけを表示するには次のようにします。
select
ename,deptno,sal
from emp;解説
SELECT句で列を指定すると、無関係なデータを返さないようになります。
不要なデータの取得にかかる時間を省けるので、ネットワークを介してデータを取得する際に特に重要になります。
列に意味のある名前を付ける
問題
クエリが返す列の名前を理解しやすい名前に変えたい。
例えば、各従業員の給与と歩合給を返すような次のクエリがあるとする。
SALとは何だろうか?「sale（販売）」の略語だろうか? 誰かの名前だろうか? COMMとは何だろうか? 結果にもっと意味のあるラベルを付けたい。
select sal,comm from emp;解決策
クエリ結果の名前を変えるには、AS句を「元の名前 AS 新しい名前」のように使います。AS句はすべてのRDBMSで使用できますが、一部のRDBMSでは省略できます。
select sal as salary, comm as commission from emp;解説
AS句を使ってクエリが返す列に新しい名前を付けることは、列のエイリアス化（aliasing）と言います。付与した新しい名前はエイリアスと呼ばれます。優れたエイリアスを作成することで、クエリとその結果を第三者に理解してもらいやすくなります。
WHERE句の中でエイリアス化された列を参照する
問題
エイリアスを使って結果セットに意味のある列名を付けており、WHERE句を使って一部の行を除外したい。
しかし、次のようにWHERE句でエイリアス名を参照しようとすると失敗してしまう。
select sal as salary, comm as commission
from emp
where salary < 5000;解決策
クエリをインラインビューとしてラップすると、エイリアス化された列を参照できます。
select * from (
select sal as salary, comm as commission
from emp
) x
where salary < 5000;解説
この簡単な例では、インラインビューを使わずに、WHERE句でCOMMやSALを直接参照して同じ結果が得られます。この解決策は、WHERE句の中で以下を参照しようとする際に実行する必要があることを示しています。
- 集約関数
- スカラサブクエリ
- ウィンドウ関数
- エイリアス
エイリアスを付けたクエリをインラインビューに入れると、外側のクエリでエイリアス化された列を参照できるようになります。なぜこのようにする必要があるのでしょうか。WHERE句はSELECTの前に評価するため、「問題」で示したクエリのWHERE句を評価するときにはSALARYとCOMMISSIONはまだ存在しません。これらのエイリアスはWHERE句の処理が完了するまで適用されません。しかし、元のクエリをFROM句に配置すると、そのクエリの結果は一番外側のWHERE句の前に作成され、一番外側のWHRER句でエイリアス名が「見える」ようになります。この手法は、テーブル内の列の名前があまり適切に付けられていない場合に特に便利です。
列値を連結する
問題
複数の列の値を1つの列として返したい。
EMPテーブルのENAMEとJOBという2つの異なる列から取得する。
select ename, job
from emp
where deptno = 10;解決策
RDBMSの組み込み関数を使い、複数の列からの値を連結します。
MySQLではCONCATという関数をサポートしています。
select
concat(ename, ' WORKS AS A ', job) as msg
from emp
where deptno=10;解説
複数の列からの値を連結するにはCONCAT関数を使います。
他のDBでは別の方法を使用するので注意が必要です。
SELECT文で条件ロジックを使う
問題
SELECT文の値に対してIF-ELSE演算を実行したい。
例えば、従業員の給与が2,000ドル以下であれば「UNDERPAID」というメッセージを返し、従業員の給与が4,000ドル以上であれば「OVERPAID」というメッセージを返し、その中間であれば「OK」を返したい。
解決策
CASE式を使って、SELECT文内で条件ロジックを直接実行します。
select ename,sal,
case when sal <= 2000 then 'UNDERPAID'
when sal >= 4000 then 'OVERPAID'
else 'OK'
end as status
from emp;解説
CASE式を使うと、クエリが返す値に対して条件ロジックを実行できます。CASE式に対してエイリアスを指定し、さらに読みやすい結果セットを返すことができます。この解決策では、CASE式の結果に対してエイリアスSTATUSを指定しています。ELSE句は省略できます。ELSEを省略すると、CASE式は検査条件を満たさない行に対してはNULLを返します。
返す行数を制限する
問題
クエリが返す行数を制限したい。順番は問わない。任意のn行でよい。
解決策
RDBMSの組み込み関数を使って、返す行数を制御します。
select * from emp limit 5;解説
多くのDBは、クエリが返す行数を指定できるFETCH FIRSTやLIMITといった句を用意しています。MySQLでは「limit」を使用します。
テーブルからn個のランダムなレコードを返す
問題
テーブルから特定の数のランダムなレコードを返したい。例えば次の文を変更して、実行するたびに異なる5行の結果セットを返すようにしたい。
select ename, job from emp;解決策
DBがサポートする、ランダムな値を返す組み込み関数を使います。
ORDER BY句の中でその関数を使い、行をランダムにソートします。そして、前述のレシピの手法を使って、ランダムにソートした行を返す行数を制限します。
select
ename,job from emp
order by rand() limit 5;解説
ORDER BY句は関数の戻り値を受け取り、その値を使って結果セットの順序を変更できます。
このクエリはすべて、ORDER BY句内の関数を実行した後に、返す行数を制限します。
ORDER BY句内での関数の使用と数値定数の使用を混同しないようにすることが重要です。ORDER BY句で数値定数を指定すると、もともとのSELECTのリスト内でその順序位置にある列でのソートを要求することになります。
ORDER BY句で関数を指定すると、各行に対して評価するため、その関数の結果に基づいてソートを実行します。
NULLを探す
問題
ある特定の列がNULLであるすべての行を探し出したい。
解決策
値がNULLであるかどうかを判断するには、IS NULLを使います。
select
*
from emp
where comm is null;解説
NULLはどんな値にも等しくなることも等しくならないこともありません。
それはNULLそのものに対しても同様です。
そのため、列がNULLかどうかを調べるのに=や!=は使えません。
行にNULLが含まれているかどうかを判断するには、IS NULLを使う必要があります。また、IS NOT NULLを使ってある列にNULLが含まれていない行を見つけることもできます。
NULLを実際の値に変換する
問題
NULLを含む行がある。NULLではなく非NULLを返したい。
解決策
COALESCE関数を使ってNULLを実際の値に置換します。
select coalesce(comm,0) from emp;解説
COALESCE関数は引数として1つ以上の値を取ります。この関数はリスト内の最初の非NULLを返します。この解決策では、COMMがNULLでなければCOMMの値を返します。NULLであればゼロを返します。
パターンを検索する
問題
ある特定の部分文字列やパターンにマッチする行を返したい。例えば次のようなクエリがあるとする。
select
ename, job
from emp
where deptno in (10,20);部署10と部署20の従業員の中で、名前の中に「I」を含むか、職種が「ER」で終わる従業員だけを返したい。
解決策
LIKE演算子をSQLワイルドカード（%）と共に使います。
select ename, job
from emp
where deptno in (10,20)
and (ename like '%I%' or job like '%ER');解説
ワイルドカード（%）は、パターンマッチ演算LIKE内で使うと任意の文字列にマッチします。
また、ほとんどのSQL実装は、1つの文字にマッチするアンダースコア（_）を用意しています。検索パターン「I」を%で囲むと、任意の位置に「I」を含むすべての文字列を返します。検索パターンを%で囲まなければ、%を配置した場所がクエリ結果に影響を与えます。例えば、「ER」で終わる職種を見つけるには、「ER」の前に%を付加します。「ER」で始まるすべての職種を探したい場合には、「ER」の後に%を付加します。
まとめ
この記事の内容は簡単かもしれませんが、SQLの基本でとても大事な内容です。
自分の覚書の意味も込めて書いているので解説が簡単になっていますので、わからない部分があれば、コメントを頂ければ解説します。
情報検索はデータベースクエリの中核なので、すべての要となります。しっかりマスターしてください！！
この記事で使用しているテーブルを作成するには
次のSQLを使ってください。
「dept」「emp」という部署と社員を表すテーブル２つを作り、ダミーデータを投入します。
今後のSQL解説記事でも、基本的に下記テーブルを利用して解説します。不足するテーブルがある場合、都度、作成用のSQLを記載します。
CREATE TABLE dept (
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13));
CREATE TABLE emp (
empno INT,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT);
INSERT INTO dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7369, 'SMITH' , 'CLERK' , 7902, STR_TO_DATE('17-DEC-2005', '%d-%b-%Y'), 800, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7499, 'ALLEN' , 'SALESMAN' , 7698, STR_TO_DATE('20-FEB-2006', '%d-%b-%Y'), 1600, 300 , 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7521, 'WARD' , 'SALESMAN' , 7698, STR_TO_DATE('22-FEB-2006', '%d-%b-%Y'), 1250, 500 , 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES' , 'MANAGER' , 7839, STR_TO_DATE('02-APR-2006', '%d-%b-%Y'), 2975, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7654, 'MARTIN', 'SALESMAN' , 7698, STR_TO_DATE('28-SEP-2006', '%d-%b-%Y'), 1250, 1400, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7698, 'BLAKE' , 'MANAGER' , 7839, STR_TO_DATE('01-MAY-2006', '%d-%b-%Y'), 2850, NULL, NULL);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK' , 'MANAGER' , 7839, STR_TO_DATE('09-JUN-2006', '%d-%b-%Y'), 2450, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT' , 'ANALYST' , 7566, STR_TO_DATE('09-DEC-2007', '%d-%b-%Y'), 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING' , 'PRESIDENT', NULL, STR_TO_DATE('17-NOV-2006', '%d-%b-%Y'), 5000, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7844, 'TURNER', 'SALESMAN' , 7698, STR_TO_DATE('08-SEP-2006', '%d-%b-%Y'), 1500, 0 , 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7876, 'ADAMS' , 'CLERK' , 7788, STR_TO_DATE('12-JAN-2008', '%d-%b-%Y'), 1100, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7900, 'JAMES' , 'CLERK' , 7698, STR_TO_DATE('03-DEC-2006', '%d-%b-%Y'), 950, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD' , 'ANALYST' , 7566, STR_TO_DATE('03-DEC-2006', '%d-%b-%Y'), 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7934, 'MILLER', 'CLERK' , 7782, STR_TO_DATE('23-JAN-2007', '%d-%b-%Y'), 1300, NULL, 10);
