全外连接
```sql
SELECT b.*,a.*
FROM beauty b
FULL OUTER JOIN boys a
ON b.boyfriend_id=a.id;
```
#交叉连接
```sql
SELECT b.*,ob.*
FROM beauty b
CROSS JOIN boys ob;
```
#SQL92和SQL99PK
- **功能**:SQL99支持的较多
- **可读性**:SQL99实现连接条件和筛选条件分离,可读性较高
--------------------------------------------
1. 查询编号>3的女神的男朋友信息(如果有则列出详细,如果没有,用nu填充):
```sql
SELECT b.id,b.name,be.*
FROM beauty b
LEFT OUTER JOIN boys be
ON b.boyfriend_id=be.id
AND b.id>3;
```
2. 查询哪个城市没有部门:
```sql
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;
```
3. 查询部门名为SAM或IT的员工信息:
```sql
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');
```