示例:
CREATE DATABASE IF NOT EXISTS test01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test01;
CREATE TABLE IF NOT EXISTS stu (
id INT (11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR (30) DEFAULT '',
class_id INT DEFAULT 0
) ENGINE = INNODB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO stu(`name`,class_id)
VALUES
('张三',1),
('李四',2),
('王五',3),
('赵柳',2),
('田七',3),
('王八',0)
;
CREATE TABLE IF NOT EXISTS class(
id INT (11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR (30) DEFAULT ''
)ENGINE = INNODB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO class (`id`,`name`)
VALUES
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D');
join写法
USE test01 ;
/*左连接*/
SELECT
s.*
FROM
stu s
LEFT JOIN class c
ON s.class_id = c.id ;
/*左连接*/
SELECT
s.*
FROM
stu s
LEFT JOIN class c
ON s.class_id = c.id
WHERE c.id IS NULL ;
/*内连接*/
SELECT
s.*
FROM
stu s
INNER JOIN class c
ON s.class_id = c.id ;
/*右连接*/
SELECT
c.*
FROM
stu s
RIGHT JOIN class c
ON s.class_id = c.id ;
/*右连接*/
SELECT
c.*
FROM
stu s
RIGHT JOIN class c
ON s.class_id = c.id
WHERE s.class_id IS NULL ;
/* mysql 不支持 full outer join*/
SELECT
s.*,
c.*
FROM
stu s
LEFT JOIN class c
ON s.class_id = c.id
UNION
SELECT
s.*,
c.*
FROM
stu s
RIGHT JOIN class c
ON s.class_id = c.id ;
SELECT
s.*,
c.*
FROM
stu s
LEFT JOIN class c
ON s.class_id = c.id
WHERE c.id IS NULL
UNION
SELECT
s.*,
c.*
FROM
stu s
RIGHT JOIN class c
ON s.class_id = c.id
WHERE s.class_id IS NULL ;