SQL1999国际标准
SELECT table1.column,table2.column
From table1
[NATURAL JOIN table2]|
[JOIN table2 USING (column_name)]|
[JOIN table2 ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]|
[CROSSS JOIN table2]
Oracle标准
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column1 = table.column2
例子：
SELECT employee_id,last_name,e.department_id,e.department_id,department_name
FROM employee e,departments d （不能用AS）
WHERE e.department_id = d.department_id
如果已经给表定义了别名，使用表的时候必须用表的别名。
内连接
表之间的字段信息有关系，等于或者不等于。
Natural Join自然连接
两张表中必须至少含有一个相同字段并且数据类型相同，才能组合在一起
例：
（SQL标准）
SELECT department_id,department_name,location_id,city
FROM departments
NATURAL JOIN locations
（Oracle标准）
SELECT d.department_id,d.department_name,d.location_id,l.city
FROM departments d,locations l
WHERE d.location_id = l.location_id
例：
两张表中有相同的字段但数据类型不同，或者指定按表中的一个字段进行连接。
（SQL标准)
SELECT employee_id,last_name,location_id,department_id
FROM employees JOIN departments
USING (department_id)
USING字句语法：USING()引用的列，在WHERE里面使用不能加表前缀。
（Oracle标准）
SELECT d.department_id,d.department_name,d.location_id,l.city
FROM departments d,locations l
WHERE d.location_id = l.location_id;
例：
两张表中没有相同的字段的连接
（SQL标准)
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
(Oracle标准）
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id;
多表连接例(三张表)：
（SQL标准）
SELECT emploee_id,city,department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON (d.location_id = l.location_id)
(Oracle标准)
SELECT employee_id,city,department_name
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
条件性JOIN连接
例：
（SQL标准）
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager id = 149 //或者 WHERE e.manager_id = 149
(Oracle标准)
SELECT d.deparment_id,d.department_name,l.city
FROM departments d,locations l
WHERE d.location_id=l.location_id AND d.department id IN(20,50)
自连接
例：
（SQL标准）
SELECT worker.last_name emp,manager.last_name mgr
FROM employees worker JOIN employees manger
ON (worker.manager_id = mgr.employee_id)
（Oracle标准）
SELECT worker.last_name || 'works for' || manager.last_name
FROM employees worker,employeees manager
WHERE worker.manager_id = manager.employee_id
Nonequijoins连接（范围比较后，再连接）
例：
（SQL标准）
SELECT e.last_name,e.salary,j.grade_level
FROM employees e JOIN job_grades
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
（Oracle标准）
SELECT e.last_name,e.salary,j.grade_level
FROM employees e JOIN job_grades
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
外连接
表之间的字段存在没有直接联系，即没有等于或者不等于的关系。
左外连接
左表所有记录都显示
（Oracle标准）
SELECT table.column,table2.column
FROM table1,table2
WHERE table1.column = table2.column(+)
例：
（SQL标准）
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUT JOIN departments d
ON (e.department_id = d.department_id)
（Oracle标准）
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+)
右外连接
右表所有记录都显示
（Oracle标准）
SELECT table.column,table2.column
FROM table1,table2
WHERE table1.column(+) = table2.column
例：
（SQL标准）
SELECT e.last_name,e.department_id,d.department_name
FROM employees e RIGHT OUT JOIN departments d
ON (e.department_id = d.department_id)
（Oracle标准）
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
WHERE e.department_id(+) = d.department_id
全外连接
左右表所有记录都显示
例：
（SQL标准）
SELECT e.last_name,e.department_id,d.department_name
FROM employees e FULL OUT JOIN departments d
ON (e.department_id = d.department_id)
笛卡尔乘积(CROSS JOIN)
第一张表的所有记录和第二张表的所有记录组合起来，n*m组合。
产生的原因：
1.内连接或外连接被忽略了
2.内连接或外连接无效
3.随意两张表两两组合，产生大量记录（用于测试）
例：
(SQL标准)
SELECT last_name,department_name
FROM employees
CROSS JOIN departments
(Oracle标准)
SELCET last_name,department_name
FROM employees,departments
程序员的基础教程：菜鸟程序员