一文完整MySQL连接查询，笛卡尔乘积，内连接外连接交叉连接

SELECT `name`,`boyName`
FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

SELECT 表1中字段,表2中字段
FROM 表1,表2 (表顺序可以换)
WHERE 表1.依据的字段=表2.依据的字段; (连接条件)

SELECT `name`,`boyName`
FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

SELECT `name`,`boyName`
FROM `beauty` AS a,`boys` As o
WHERE a.`boyfriend_id`=o.`id`;

SELECT `last_name`,`department_name`,`commission_pct`
FROM `departments` AS d,`employees` AS e
WHERE d.`department_id`=e.`department_id`
AND `commission_pct` IS NOT NULL;

#查询每个城市有多少个部门
SELECT COUNT(*) AS 个数,`city`
FROM `departments` AS d,`locations` AS l
WHERE d.`location_id`=l.`location_id`
GROUP BY `city`;

SELECT `last_name`,`department_name`
FROM `employees` AS e
INNER JOIN `departments` AS d           #inner为内连接类型标识
ON e.`department_id`=d.`department_id`; #连接条件

#多表
SELECT `last_name`,`department_name`,`city`
FROM `employees` AS e
INNER JOIN `departments` AS d ON e.`department_id`=d.`department_id`
INNER JOIN `locations` AS l ON d.`location_id`=l.`location_id`
ORDER BY `department_name` DESC;

SELECT salary,grade_level
FROM employees AS e,job_grades AS g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

SELECT `salary`,`grade_level`
FROM `employees` AS e
INNER JOIN `job_grades` AS j
ON `salary` BETWEEN `lowest_sal` AND `highest_sal`;

SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` AS e,`employees` AS m
WHERE e.`manager_id`=m.`employee_id`

SELECT e.`last_name` AS 员工,m.`last_name` AS 领导
FROM `employees` AS e
INNER JOIN `employees` AS m
ON e.`manager_id`=m.`employee_id`;

#左外连接
SELECT be.`name`,bo.`boyName`
FROM `beauty` AS be
LEFT JOIN `boys` AS bo
ON be.`boyfriend_id`=bo.`id`;

#右外连接
SELECT be.`name`,bo.`boyName`
FROM `boys` AS bo
RIGHT JOIN `beauty` AS be
ON be.`boyfriend_id`=bo.`id`;

SELECT be.`name`,bo.`boyName`
FROM `boys` AS bo
CROSS JOIN `beauty` AS be;