MySQL
結合

構文

• INNERとOUTERは無視されます。

• FULLはMySQLでは実装されていません。

• "commajoin"（ FROM a,b WHERE ax=by where FROM a,b WHERE ax=by ）は眉をひそめられています。代わりに、 FROM a JOIN b ON ax=by使用してください。

• FROM a JOIN B ON ax = by両方のテーブルで一致する行を含む。

• LEFT JOINからb ax = = byは、 aすべての行とb一致するデータを含み、一致する行がない場合はNULLs含みます。

結合例

DB上のテーブルを作成するためのクエリ

CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`course` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `course` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

InnoDBテーブルを使用しており、user.courseとcourse.idが関連していることを知っているので、外部キー関係を指定できます。

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

結合クエリ（内部結合）

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

サブクエリを持つJOIN（ "派生"テーブル）

SELECT x, ...
    FROM ( SELECT y, ... FROM ... ) AS a
    JOIN tbl  ON tbl.x = a.y
    WHERE ...

これは、サブクエリをテンポラリテーブルに評価し、次にそれをtbl JOINします。

5.6以前では、一時表に索引を作成できませんでした。したがって、これは潜在的に非常に非効率的でした。

SELECT ...
    FROM ( SELECT y, ... FROM ... ) AS a
    JOIN ( SELECT x, ... FROM ... ) AS b  ON b.x = a.y
    WHERE ...

5.6では、オプティマイザが最適なインデックスを見つけ、即座に作成します。 （これにはオーバーヘッドがあるため、まだ完全ではありません。）

別の一般的なパラダイムは、何かを初期化するための副問い合わせを持つことです：

SELECT 
        @n := @n + 1,
        ...
    FROM ( SELECT @n := 0 ) AS initialize
    JOIN the_real_table
    ORDER BY ...

（注：これは技術的にはONないことによって示されるCROSS JOIN （デカルト積）ですが、副問合せはthe_real_table n個の行に一致する必要がある行を1つだけ戻すので効率的the_real_table 。

オーダーのある顧客の検索 - テーマのバリエーション

これにより、すべての顧客のすべての注文が得られます。

SELECT c.CustomerName, o.OrderID
    FROM Customers AS c
    INNER JOIN Orders AS o
        ON c.CustomerID = o.CustomerID
    ORDER BY c.CustomerName, o.OrderID;

これにより、各顧客の注文数がカウントされます。

SELECT c.CustomerName, COUNT(*) AS 'Order Count'
    FROM Customers AS c
    INNER JOIN Orders AS o
        ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID;
    ORDER BY c.CustomerName;

また、数えますが、おそらくより高速です：

SELECT  c.CustomerName,
        ( SELECT COUNT(*) FROM Orders WHERE CustomerID = c.CustomerID ) AS 'Order Count'
    FROM Customers AS c
    ORDER BY c.CustomerName;

オーダーのある顧客のみを一覧表示します。

SELECT  c.CustomerName,
    FROM Customers AS c
    WHERE EXISTS ( SELECT * FROM Orders WHERE CustomerID = c.CustomerID )
    ORDER BY c.CustomerName;

完全な外部結合

MySQLはFULL OUTER JOINサポートしていませんが、エミュレートする方法はあります。

データの設定

-- ----------------------------
-- Table structure for `owners`
-- ----------------------------
DROP TABLE IF EXISTS `owners`;
CREATE TABLE `owners` (
`owner_id` int(11) NOT NULL AUTO_INCREMENT,
`owner` varchar(30) DEFAULT NULL,
PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of owners
-- ----------------------------
INSERT INTO `owners` VALUES ('1', 'Ben');
INSERT INTO `owners` VALUES ('2', 'Jim');
INSERT INTO `owners` VALUES ('3', 'Harry');
INSERT INTO `owners` VALUES ('6', 'John');
INSERT INTO `owners` VALUES ('9', 'Ellie');
-- ----------------------------
-- Table structure for `tools`
-- ----------------------------
DROP TABLE IF EXISTS `tools`;
CREATE TABLE `tools` (
`tool_id` int(11) NOT NULL AUTO_INCREMENT,
`tool` varchar(30) DEFAULT NULL,
`owner_id` int(11) DEFAULT NULL,
PRIMARY KEY (`tool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tools
-- ----------------------------
INSERT INTO `tools` VALUES ('1', 'Hammer', '9');
INSERT INTO `tools` VALUES ('2', 'Pliers', '1');
INSERT INTO `tools` VALUES ('3', 'Knife', '1');
INSERT INTO `tools` VALUES ('4', 'Chisel', '2');
INSERT INTO `tools` VALUES ('5', 'Hacksaw', '1');
INSERT INTO `tools` VALUES ('6', 'Level', null);
INSERT INTO `tools` VALUES ('7', 'Wrench', null);
INSERT INTO `tools` VALUES ('8', 'Tape Measure', '9');
INSERT INTO `tools` VALUES ('9', 'Screwdriver', null);
INSERT INTO `tools` VALUES ('10', 'Clamp', null);

何を見たいのですか？

私たちは誰がどのツールを所有しているのか、そしてどのツールが所有者を持たないのかを知るためのリストを得たいと考えています。

クエリ

これを達成するために、 UNIONを使用して2つのクエリを組み合わせることができます。この最初のクエリでは、 LEFT JOINを使用して所有者のツールに参加しています。これにより、すべての所有者が結果セットに追加されます。実際にツールを所有しているかどうかは関係ありません。

2番目のクエリでは、ツールを所有者に参加させるためにRIGHT JOINを使用しています。このようにして、結果セット内のすべてのツールを取得できNULL 。所有者が所有者でない場合、所有者列には単にNULLが含まれNULL 。追加することによりWHEREによってフィルタリングされ-clause owners.owner_id IS NULL我々は唯一の右の結合テーブル内のデータを探しているとして、すでに、最初のクエリによって返されていないものをデータセットとして結果を定義しています。

UNION ALLを使用しているので、2番目のクエリの結果セットは最初のクエリ結果セットにアタッチされます。

SELECT `owners`.`owner`, tools.tool
FROM `owners`
LEFT JOIN `tools` ON `owners`.`owner_id` = `tools`.`owner_id`
UNION ALL
SELECT `owners`.`owner`, tools.tool
FROM `owners`
RIGHT JOIN `tools` ON `owners`.`owner_id` = `tools`.`owner_id`
WHERE `owners`.`owner_id` IS NULL;

+-------+--------------+
| owner | tool         |
+-------+--------------+
| Ben   | Pliers       |
| Ben   | Knife        |
| Ben   | Hacksaw      |
| Jim   | Chisel       |
| Harry | NULL         |
| John  | NULL         |
| Ellie | Hammer       |
| Ellie | Tape Measure |
| NULL  | Level        |
| NULL  | Wrench       |
| NULL  | Screwdriver  |
| NULL  | Clamp        |
+-------+--------------+
12 rows in set (0.00 sec)

3つのテーブルの内部結合

タグ付きのシンプルなウェブサイトに使用できる3つのテーブルがあるとしましょう。

• フィストテーブルはポスト用です。
• タグの2番目
• タグ＆ポスト関係の3番目

拳のテーブル "ビデオゲーム"

"タグ"テーブル

"tags_meta"テーブル

SELECT videogame.id,
    videogame.title,
    videogame.reg_date,
    tags.name,
    tags_meta.post_id
FROM tags_meta
INNER JOIN videogame ON videogame.id = tags_meta.post_id
INNER JOIN tags ON tags.id = tags_meta.tag_id
WHERE tags.name = "elizabeth"
ORDER BY videogame.reg_date

このコードは、そのタグ "#elizabeth"に関連するすべての投稿を返すことができます

結合された結合

あなたが視覚指向の人であれば、このVennダイアグラムは、MySQL内に存在するさまざまな種類のJOINを理解するのに役立ちます。