抽出、ソート、結合、制限テーブル結合(複数のテーブルの使用)
- テーブル結合〔JOIN0〕
- テーブルの内部結合〔INNER JOIN〕
- テーブルの外部結合〔OUTER JOIN〕
- このページで使用しているサンプルデータ
テーブル結合
JOIN0
unknown
複数のテーブルから、関連するデータをヒモ付けるには、JOIN文を使用します。
テーブル名にエイリアス(別名)を付けると、テーブル名を参照する時に便利です。
テーブル名 AS 別名
または テーブル名 エイリアス
のように指定します。
# tbl_Customerテーブルの内容 mysql> SELECT * FROM tbl_Customer; Enter +--------+-----------+----------+------+------+ | UserID | FirstName | LastName | Sex | Age | +--------+-----------+----------+------+------+ | 1 | 花子 | 鈴木 | f | 21 | | 2 | 太郎 | 田中 | m | 30 | | 3 | 一郎 | 鈴木 | m | 45 | | 4 | 萌子 | 山口 | f | 18 | | 5 | 愛 | 森永 | f | 21 | | 6 | 太郎 | 鈴木 | m | 22 | | 7 | 花子 | 佐藤 | f | 35 | +--------+-----------+----------+------+------+ 7 rows in set (0.00 sec) # tbl_Addressテーブルの内容 mysql> SELECT * FROM tbl_Address; Enter +--------+----------+----------+-------------------------+ | UserID | Zip | Ken | Addr | +--------+----------+----------+-------------------------+ | 1 | 155-0031 | 東京都 | 世田谷区下北1-1-1 | | 2 | 160-0023 | 東京都 | 新宿区西新宿9-8-7 | | 3 | 079-1274 | 北海道 | 赤平市エルム町4-2-5 | | 4 | 061-3254 | 北海道 | 石狩市樽川3-1-1 | | 5 | 155-0031 | 神奈川県 | 横浜市緑区いぶき野2-100 | | 6 | 155-0031 | 神奈川県 | 横浜市緑区白山10-33-2 | | 7 | 155-0031 | 沖縄県 | 石垣市石垣9000 | +--------+----------+----------+-------------------------+ 7 rows in set (0.00 sec) # tbl_Customerテーブルとtbl_Addressテーブルを結合し # FirstName、LastName、Ken、Addrフィールドを表示 mysql> SELECT c.FirstName, c.LastName, a.Ken, a.Addr Enter -> FROM tbl_Customer AS c, tbl_Address AS a Enter -> WHERE c.UserID=a.UserID; Enter +-----------+----------+----------+-------------------------+ | FirstName | LastName | Ken | Addr | +-----------+----------+----------+-------------------------+ | 花子 | 鈴木 | 東京都 | 世田谷区下北1-1-1 | | 太郎 | 田中 | 東京都 | 新宿区西新宿9-8-7 | | 一郎 | 鈴木 | 北海道 | 赤平市エルム町4-2-5 | | 萌子 | 山口 | 北海道 | 石狩市樽川3-1-1 | | 愛 | 森永 | 神奈川県 | 横浜市緑区いぶき野2-100 | | 太郎 | 鈴木 | 神奈川県 | 横浜市緑区白山10-33-2 | | 花子 | 佐藤 | 沖縄県 | 石垣市石垣9000 | +-----------+----------+----------+-------------------------+ 7 rows in set (0.01 sec)
テーブルの内部結合
INNER JOIN
unknown
# tbl_Customerテーブルの内容 mysql> SELECT * FROM tbl_Customer; Enter +--------+-----------+----------+------+------+ | UserID | FirstName | LastName | Sex | Age | +--------+-----------+----------+------+------+ | 1 | 花子 | 鈴木 | f | 21 | | 2 | 太郎 | 田中 | m | 30 | | 3 | 一郎 | 鈴木 | m | 45 | | 4 | 萌子 | 山口 | f | 18 | | 5 | 愛 | 森永 | f | 21 | | 6 | 太郎 | 鈴木 | m | 22 | | 7 | 花子 | 佐藤 | f | 35 | +--------+-----------+----------+------+------+ 7 rows in set (0.00 sec) # tbl_Addressテーブルの内容 mysql> SELECT * FROM tbl_Address; Enter +--------+----------+----------+-------------------------+ | UserID | Zip | Ken | Addr | +--------+----------+----------+-------------------------+ | 1 | 155-0031 | 東京都 | 世田谷区下北1-1-1 | | 2 | 160-0023 | 東京都 | 新宿区西新宿9-8-7 | | 3 | 079-1274 | 北海道 | 赤平市エルム町4-2-5 | | 4 | 061-3254 | 北海道 | 石狩市樽川3-1-1 | | 5 | 155-0031 | 神奈川県 | 横浜市緑区いぶき野2-100 | | 6 | 155-0031 | 神奈川県 | 横浜市緑区白山10-33-2 | | 7 | 155-0031 | 沖縄県 | 石垣市石垣9000 | +--------+----------+----------+-------------------------+ 7 rows in set (0.00 sec) # tbl_Salesテーブルの内容 mysql> SELECT * FROM tbl_Sales; Enter +---------+--------+--------+ | OrderID | UserID | Amount | +---------+--------+--------+ | 1 | 1 | 45000 | | 2 | 1 | 4000 | | 3 | 3 | 1000 | | 4 | 5 | 3000 | | 5 | 5 | 20000 | | 6 | 5 | 3000 | | 7 | 5 | 5000 | +---------+--------+--------+ 7 rows in set (0.00 sec) # tbl_Customer、tbl_Addrss、tbl_Salesテーブルを内部結合 # UserID、LastName、FirstName、Ken、Addr、Sales(別名)を表示 # ※内部結合の場合は、外部結合と異なり、 # tbl_Salesテーブルに存在しないユーザーは表示されません mysql> SELECT c.UserID, c.LastName, c.FirstName, a.Ken, Enter -> a.Addr, sum(s.Amount) as Sales Enter -> FROM tbl_Customer c Enter -> INNER JOIN tbl_Address a ON c.UserID=a.UserID Enter -> INNER JOIN tbl_Sales s ON c.UserID=s.UserID Enter -> GROUP BY c.UserID; Enter +--------+----------+-----------+----------+-------------------------+-------+ | UserID | LastName | FirstName | Ken | Addr | Sales | +--------+----------+-----------+----------+-------------------------+-------+ | 1 | 鈴木 | 花子 | 東京都 | 世田谷区下北1-1-1 | 49000 | | 3 | 鈴木 | 一郎 | 北海道 | 赤平市エルム町4-2-5 | 1000 | | 5 | 森永 | 愛 | 神奈川県 | 横浜市緑区いぶき野2-100 | 31000 | +--------+----------+-----------+----------+-------------------------+-------+ 3 rows in set (0.01 sec)
テーブルの外部結合
OUTER JOIN
unknown
# tbl_Customerテーブルの内容 mysql> SELECT * FROM tbl_Customer; Enter +--------+-----------+----------+------+------+ | UserID | FirstName | LastName | Sex | Age | +--------+-----------+----------+------+------+ | 1 | 花子 | 鈴木 | f | 21 | | 2 | 太郎 | 田中 | m | 30 | | 3 | 一郎 | 鈴木 | m | 45 | | 4 | 萌子 | 山口 | f | 18 | | 5 | 愛 | 森永 | f | 21 | | 6 | 太郎 | 鈴木 | m | 22 | | 7 | 花子 | 佐藤 | f | 35 | +--------+-----------+----------+------+------+ 7 rows in set (0.00 sec) # tbl_Addressテーブルの内容 mysql> SELECT * FROM tbl_Address; Enter +--------+----------+----------+-------------------------+ | UserID | Zip | Ken | Addr | +--------+----------+----------+-------------------------+ | 1 | 155-0031 | 東京都 | 世田谷区下北1-1-1 | | 2 | 160-0023 | 東京都 | 新宿区西新宿9-8-7 | | 3 | 079-1274 | 北海道 | 赤平市エルム町4-2-5 | | 4 | 061-3254 | 北海道 | 石狩市樽川3-1-1 | | 5 | 155-0031 | 神奈川県 | 横浜市緑区いぶき野2-100 | | 6 | 155-0031 | 神奈川県 | 横浜市緑区白山10-33-2 | | 7 | 155-0031 | 沖縄県 | 石垣市石垣9000 | +--------+----------+----------+-------------------------+ 7 rows in set (0.00 sec) # tbl_Salesテーブルの内容 mysql> SELECT * FROM tbl_Sales; Enter +---------+--------+--------+ | OrderID | UserID | Amount | +---------+--------+--------+ | 1 | 1 | 45000 | | 2 | 1 | 4000 | | 3 | 3 | 1000 | | 4 | 5 | 3000 | | 5 | 5 | 20000 | | 6 | 5 | 3000 | | 7 | 5 | 5000 | +---------+--------+--------+ 7 rows in set (0.00 sec) # tbl_Customer、tbl_Addressテーブルを内部結合し、tbl_Salesテーブルを外部結合 # UserID、LastName、FirstName、Ken、Addr、Sales(別名)を表示 # ※外部結合の場合は、内部結合と異なり、 # tbl_Salesテーブルに存在しないユーザーも表示されます # 該当するデータないカラムにはNULLが挿入されます mysql> SELECT c.UserID, c.LastName, c.FirstName, Enter -> a.Ken, a.Addr, sum(s.Amount) as Sales Enter -> FROM tbl_Customer c Enter -> INNER JOIN tbl_Address a ON c.UserID=a.UserID Enter -> LEFT OUTER JOIN tbl_Sales s ON c.UserID=s.UserID Enter -> GROUP BY c.UserID; Enter +--------+----------+-----------+----------+-------------------------+-------+ | UserID | LastName | FirstName | Ken | Addr | Sales | +--------+----------+-----------+----------+-------------------------+-------+ | 1 | 鈴木 | 花子 | 東京都 | 世田谷区下北1-1-1 | 49000 | | 2 | 田中 | 太郎 | 東京都 | 新宿区西新宿9-8-7 | NULL | | 3 | 鈴木 | 一郎 | 北海道 | 赤平市エルム町4-2-5 | 1000 | | 4 | 山口 | 萌子 | 北海道 | 石狩市樽川3-1-1 | NULL | | 5 | 森永 | 愛 | 神奈川県 | 横浜市緑区いぶき野2-100 | 31000 | | 6 | 鈴木 | 太郎 | 神奈川県 | 横浜市緑区白山10-33-2 | NULL | | 7 | 佐藤 | 花子 | 沖縄県 | 石垣市石垣9000 | NULL | +--------+----------+-----------+----------+-------------------------+-------+ 7 rows in set (0.00 sec)
このページで使用しているサンプルデータ
unknown
サンプルデータ
# データベース(db_User)作成 CREATE DATABASE db_User; # データベース(db_User)選択 USE db_User; # テーブル(tbl_Customer)作成 CREATE TABLE tbl_Customer( UserID integer NOT NULL PRIMARY KEY, FirstName varchar(32) NOT NULL, LastName varchar(32) NOT NULL, Sex char(2) DEFAULT NULL, Age integer DEFAULT NULL ); # データ挿入 INSERT INTO tbl_Address (UserID,FirstName,LastName,Sex,Age) VALUES (1,'花子','鈴木','f',21), (2,'太郎','田中','m',30), (3,'一郎','鈴木','m',45), (4,'萌子','山口','f',18), (5,'愛','森永','f',21), (6,'太郎','鈴木','m',22), (7,'花子','佐藤','f',35); # テーブル(tbl_Address)作成 CREATE TABLE IF NOT EXISTS tbl_Address( UserID INT NOT NULL PRIMARY KEY, Zip CHAR(9) DEFAULT NULL, Ken CHAR(10) DEFAULT NULL, Addr VARCHAR(200) DEFAULT NULL ); # データ挿入 INSERT INTO tbl_Address (UserID,Zip,Ken,Addr) VALUES (1,'155-0031','東京都','世田谷区下北1-1-1'), (2,'160-0023','東京都','新宿区西新宿9-8-7'), (3,'079-1274','北海道','赤平市エルム町4-2-5'), (4,'061-3254','北海道','石狩市樽川3-1-1'), (5,'155-0031','神奈川県','横浜市緑区いぶき野2-100'), (6,'155-0031','神奈川県','横浜市緑区白山10-33-2'), (7,'155-0031','沖縄県','石垣市石垣9000'); # テーブル(tbl_Sales)作成 CREATE TABLE tbl_Sales( OrderID integer NOT NULL PRIMARY KEY, UserID integer NOT NULL, Amount DECIMAL DEFAULT NULL ); # データ挿入 INSERT INTO tbl_Sales VALUES (1,1,45000), (2,1,4000), (3,3,1000), (4,5,3000), (5,5,20000), (6,5,3000), (7,5,5000);