前提
建了两个表:student,worker。
CREATE TABLE
IF NOT EXISTS student (
student_id INT (11) NOT NULL AUTO_INCREMENT,
student_name VARCHAR (16) NOT NULL,
PRIMARY KEY (student_id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
CREATE TABLE
IF NOT EXISTS worker (
worker_id INT (11) NOT NULL AUTO_INCREMENT,
worker_name VARCHAR (16) NOT NULL,
PRIMARY KEY (worker_id)
) ENGINE = INNODB;
插入数据:
INSERT INTO student (student_id, student_name)
VALUES
(1, 'one'),
(2, 'two'),
(3, 'three'),
(4, 'four'),
(5, 'A');
INSERT INTO worker (worker_id, worker_name)
VALUES
(1, 'one'),
(2, 'two'),
(3, 'A'),
(4, 'B');
三种连接
内连接:
SELECT
*
FROM
student s
INNER JOIN worker w ON s.student_name = w.worker_name;
结果:
左连接:
SELECT
*
FROM
student s
LEFT JOIN worker w ON s.student_name = w.worker_name;
结果:
右连接:
SELECT
*
FROM
student s
RIGHT JOIN worker w ON s.student_name = w.worker_name;
结果:
结论
这三种连接就类似于下图: