MySQL表连接

SELECT DISTINCT
	< select_list > 
FROM
	< left_table > < join_type >
JOIN < right_table > ON <join_condition>
WHERE
	< where_condition > 
GROUP BY
	< group_by_list > 
HAVING
	< having_condition > 
ORDER BY
	< order_by_condition > 
LIMIT < limit_number >

CREATE TABLE `employ` (
  `id` int(10) NOT NULL,
  `name` char(10) DEFAULT NULL,
  `dept_no` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `employ` VALUES (1,'张三',1),(2,'李四',2);

CREATE TABLE `dept` (
  `dept_no` int(10) NOT NULL,
  `dept_name` char(10) DEFAULT NULL,
  `dept_admin` char(10) DEFAULT NULL,
  PRIMARY KEY (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dept` VALUES (2,'人力资源部','李四'),(3,'财务部',NULL);

笛卡尔积操作 N*M

select employ.*, dept.* from employ join dept ;

$A\cup (A\cap B)$

select a.*, b.* from employ a left join dept b on a.dept_no = b.dept_no ;

$B\cup (A\cap B)$

select a.*, b.* from employ a right join dept b on a.dept_no = b.dept_no ;

$A\cap B$

select a.*, b.* from employ a inner join dept b on a.dept_no = b.dept_no ;

$A-(A\cap B)$

select a.*, b.* from employ a left join dept b on a.dept_no = b.dept_no where b.dept_no is null ;

$B-(A\cap B)$

select a.*, b.* from employ a right join dept b on a.dept_no = b.dept_no where a.dept_no is null ;

$A\cup B-(A\cap B)$

select a.*, b.* from employ a left join dept b on a.dept_no = b.dept_no where b.dept_no is null
union 
select a.*, b.* from employ a right join dept b on a.dept_no = b.dept_no where a.dept_no is null ;

$A\cup B$

select a.*, b.* from employ a left join dept b on a.dept_no = b.dept_no  
union 
select a.*, b.* from employ a right join dept b on a.dept_no = b.dept_no ;