戻る

複数のテーブルを利用する

テーブルの準備

tb(売上情報テーブル)

CREATE TABLE tb (bang VARCHAR(10), uria INT, tuki INT) CHARSET=utf8;

INSERT INTO tb(bang, uria, tuki) VALUES
('A103', 101, 4),
('A102', 54, 5),
('A104', 181, 4),
('A101', 184, 4),
('A103', 17, 5),
('A101', 300, 5),
('A102', 205, 6),
('A104', 93, 5),
('A103', 12, 6),
('A107', 87, 6);

tb1(社員情報テーブル)

CREATE TABLE tb1(bang VARCHAR(10), nama VARCHAR(10), tosi INT) CHARSET=utf8;

INSERT INTO tb1 VALUES('A101','佐藤','40');
INSERT INTO tb1 VALUES('A102','高橋','28');
INSERT INTO tb1 VALUES('A103','中川','20');
INSERT INTO tb1 VALUES('A104','渡辺','23');
INSERT INTO tb1 VALUES('A105','西沢','35');

tb2(別の営業所の社員情報テーブル)

CREATE TABLE tb2 (bang VARCHAR(10), nama VARCHAR(10), tosi INT) CHARSET=utf8;

INSERT INTO tb2 (bang,nama,tosi) VALUES
('A106','中村',26),
('A107','田中',24),
('A108','鈴木',23),
('A109','村井',25),
('A110','吉田',27);

tb3(社員の出身県情報テーブル)

CREATE TABLE tb3 (bang VARCHAR(10), ken VARCHAR(10)) CHARSET=utf8;

INSERT INTO tb3 (bang,ken) VALUES
('A101','東京都'),
('A102','埼玉県'),
('A103','神奈川県'),
('A104','北海道'),
('A105','静岡県');

2つのテーブルのレコードを集めて表示

SELECT カラム名1 FROM テーブル名1 UNION SELECT カラム名2 FROM テーブル名2;

★

SELECT * FROM tb1 UNION SELECT * FROM tb2;

(SELECT * FROM tb1) UNION (SELECT * FROM tb2);

3つ以上のテーブルから「UNION」で集める

★

(SELECT * FROM tb WHERE bang='A102')
UNION
(SELECT * FROM tb WHERE bang='A103')
UNION
(SELECT * FROM tb WHERE bang='A104')
UNION
(SELECT * FROM tb WHERE bang='A107');

SELECT * FROM tb WHERE bang NOT IN('A101');

SELECT * FROM tb WHERE bang IN('A102','A103','A104','A107');

条件をつけて複数の抽出結果を合わせて表示する

★

(SELECT bang FROM tb WHERE uria>=200)
UNION
(SELECT bang FROM tb1 WHERE tosi>=35);

複数の抽出結果をあわせて表示する（重複を許容）

★

(SELECT bang FROM tb WHERE uria>=200)
UNION ALL
(SELECT bang FROM tb1 WHERE tosi>=35);

2つのテーブルを結合する

SELECT カラム名 FROM テーブル1 JOIN 結合するテーブル2 ON テーブル1のカラム=テーブル2のカラム;

★

SELECT * FROM tb JOIN tb1 ON tb.bang=tb1.bang;

SELECT * FROM tb INNER JOIN tb1 ON tb.bang=tb1.bang;

カラムを選んで表示する

★

SELECT tb.bang, tb1.nama, tb.uria FROM tb JOIN tb1 ON tb.bang=tb1.bang;

テーブル名にエイリアスを付けるには

テーブル名 AS エイリアス

★

SELECT x.bang, y.nama, x.uria FROM tb as x JOIN tb1 as y ON x.bang=y.bang;

USINGを使ってON～の部分を見えやすくする

★

SELECT tb.bang, tb1.nama, tb.uria FROM tb JOIN tb1 USING(bang);

結合しtがテーブルから「WHERE」で設定した条件で抽出する

★

SELECT tb.bang AS 社員番号, tb1.nama AS 氏名, tb.uria AS 売上 FROM tb
JOIN tb1 USING(bang) WHERE tb.uria>=100;

複数のテーブルを内部結合する

SELECT ～ FROM
テーブル名1
JOIN テーブル名2 結合の条件
JOIN テーブル名3 結合の条件
...
;

★

SELECT tb.bang, tb.uria, tb1.nama, tb3.ken FROM tb
JOIN tb1 USING(bang)
JOIN tb3 USING(bang);

内部結合と外部結合

• 「JOIN」（あるいはINNER JOIN）による「内部結合は」、キーが一致しているレコードだけを取り出す
• 「外部結合」は、一致していなくても、一方のテーブルのレコードはすべて取り出す

外部結合の種類

左外部結合（LEFT JOIN）

「一致したレコード」および、「テーブル1（左側に相当）の全データ」を表示します

右外部結合（RIGHT JOIN）

「一致したレコード」および、「テーブル2（右側に相当）の全データ」を表示します

左外部結合

SELECT カラム名 FROM テーブル1
LEFT JOIN 結合するテーブル2 ON テーブル1のカラム=テーブル2のカラム;

★

SELECT tb.bang,tb1.nama FROM tb LEFT JOIN tb1 USING(bang);

SELECT tb.bang,tb1.nama FROM tb LEFT OUTER JOIN tb1 USING(bang);

右外部結合

SELECT カラム名 FROM テーブル1
RIGHT JOIN 結合するテーブル2 ON テーブル1のカラム=テーブル2のカラム;

★

SELECT tb.bang,tb1.nama FROM tb RIGHT JOIN tb1 USING(bang);

SELECT tb.bang,tb1.nama FROM tb RIGHT OUTER JOIN tb1 USING(bang);

自己結合

SELECT カラム名 FROM テーブル名 AS エイリアス1 JOIN テーブル名 AS エイリアス2;

★

SELECT * FROM tb1 AS a JOIN tb1 AS b;

順位付けの妙義　その１

★

SELECT a.nama, a.tosi, COUNT(*) FROM tb1 AS a
JOIN tb1 AS b
WHERE a.tosi<=b.tosi
GROUP BY a.bang;

最大の値を持つものを表示　-値を返すサブクエリ　その１-

★

SELECT * FROM tb
WHERE uria
IN (SELECT MAX(uria) FROM tb);

平均以上のレコードを抽出する　-値を返すサブクエリ　sその２-

★

SELECT *
FROM tb1
WHERE tosi
>=(SELECT AVG(tosi) FROM tb1);

INを使う　-カラムを返すサブクエリ-

SELECT 表示するカラム FROM テーブル名
WHERE カラム名 IN(SELECTによるサブクエリでカラムを抽出);

★

SELECT * FROM tb1
WHERE bang
IN (SELECT bang FROM tb WHERE uria>=200);

EXISTSで、存在するレコードだけを対象にする

★

SELECT * FROM tb1
WHERE EXISTS (SELECT * FROM tb WHERE tb.bang=tb1.bang);

今度はNOT EXISTS

★

SELECT * FROM tb1
WHERE NOT EXISTS (SELECT * FROM tb WHERE tb.bang=tb1.bang);

順位付けの妙義　その２

CREATE TABLE tb_zyun LIKE tb;

ALTER TABLE tb_zyun ADD zyun INT AUTO_INCREMENT PRIMARY KEY;

INSERT INTO tb_zyun (bang,uria,tuki)
(SELECT bang,uria,tuki FROM tb ORDER BY uria DESC);

---

問

テーブル「tb」で、「uria」の少ないものから順位を付け、1番から順に、「bang」、「uria」、順位を表示させてください。

SELECT a.bang,a.uria,COUNT(*)
FROM tb AS a
JOIN tb AS b
WHERE a.uria>b.uria
GROUP BY a.uria;

問

次のテーブル「tb1」および「tb」で、テーブル「tb1」に存在するレコードだけテーブル「tb」から抽出してください。

SELECT * FROM tb
WHERE EXISTS
(SELECT * FROM tb1 WHERE tb.bang=tb1.bang);

Copyright © 2012 YOHSUKE NAKANO