mysql join查询图示加示例

示例：

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 ;