mysql全外和交叉&&sql92pksql99

#全外
use girls;
SELECT b.*,a.*
FROM beauty b
FULL OUTER JOIN boys a
on b.boyfrien_id=a.id;

#交叉连接99标准笛卡尔乘积
SELECT b.*,ob.*
FROM beauty b
CROSS JOIN boys ob;

#sql92和sql99pk
#功能：sql99支持的较多
#可读性：sql99实现连接条件和筛选条件分离，可读性较高

#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用nu11填充、
SELECT b.id,b.name,be.*
FROM beauty b
LEFT OUTER JOIN boys be
ON b.boyfriend_id=be.id
and b.id>3;

#二、查询哪个城市没有部门
USE myemployees;
SELECT city
FROM locations l
LEFT OUTER JOIN departments d
on d.location_id=l.location_id
WHERE d.department_id is null;

#三、查询部门名为SAM或IT的员工信息
SELECT e.*,d.department_name
FROM employees e
LEFT OUTER JOIN departments d
on e.department_id=d.department_id
WHERE d.department_name in('SAL','IT');