MySQL
조인

통사론

• INNER 및 OUTER 는 무시됩니다.

• MySQL에서는 FULL 이 구현되지 않았습니다.

• "commajoin"( FROM a,b WHERE ax=by where FROM a,b WHERE ax=by )은 눈살을 찌푸리게됩니다. 대신에 FROM a JOIN b ON ax=by 사용하십시오.

• 두 테이블 모두에서 일치하는 행을 포함하여 by ax = = by 조인 b에서.

• 좌로부터 의해 모든 행의 행 포함 AX = ON B JOIN 플러스로부터 매칭 데이터 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;

하위 쿼리가있는 조인 ( "파생 테이블")

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 (Cartesian 제품)이지만, 하위 쿼리는 the_real_table 의 n 행과 일치 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 을 사용하여 두 개의 쿼리를 결합 할 수 있습니다. 이 첫 번째 쿼리에서 LEFT JOIN 을 사용하여 소유자에 대한 도구에 참여합니다. 이렇게하면 모든 소유자가 결과 세트에 추가되고 실제로 도구를 소유하고 있는지 여부는 중요하지 않습니다.

두 번째 쿼리에서는 도구를 소유자에게 연결하는 데 RIGHT JOIN 을 사용합니다. 이렇게하면 우리는 결과 집합의 모든 도구를 가져올 수 있습니다. 소유자가 소유하지 않은 경우 소유자 열에는 단순히 NULL 포함 NULL . owners.owner_id IS NULL 필터링하는 WHERE 을 추가하여 오른쪽 조인 된 테이블의 데이터 만 찾고 있으므로 첫 번째 쿼리에서 아직 반환하지 않은 데이터 집합으로 결과를 정의합니다.

UNION ALL 사용하기 때문에 두 번째 쿼리의 결과 집합이 첫 번째 쿼리 결과 집합에 연결됩니다.

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 개의 테이블에 대한 내부 조인

태그가있는 간단한 웹 사이트에 사용할 수있는 세 개의 테이블이 있다고 가정 해 보겠습니다.

• 주먹 테이블은 게시물 용입니다.
• 두 번째 태그
• 태그 및 게시 관계에 대한 셋째

주먹 테이블 "비디오 게임"

"태그"표

"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 을 이해하는 데 도움이 될 수 있습니다.