mysql全外和交叉&&sql92pksql99

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');