syntax:
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)]|
[CROSS JOIN table2];
1》[NATURAL JOIN table2]---自然连接,(条件:需要做连接的表中,有name相同列并且column的
数据型要一致)
注意:如果where中要使用公共列做为条件,不能够使用表名或表的别名做前缀
2》[JOIN table2 USING (column_name)]---对自然连接的拓展,如果自然连接中公共列有多个column,
如果仅仅想使用其中一列作为连接条件或数据类型不一致,可以使用 using指定要使用的column
3》[JOIN table2
ON (table1.column_name = table2.column_name)]---如果要对结果进行过滤,可以使用on限定条件
4》[CROSS JOIN table2]---笛卡尔积
5》
LEFT OUTER join
RIGHT OUTER join
FULL OUTER join
----------------------------
Creating Joins with the USING Clause
---If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.
---Use the USING clause to match only one column when more than one column matches.
---The NATURAL JOIN and USING clauses are mutually exclusive.
Using Table Aliases with the USING Clause
---Do not qualify a column that is used in the USING clause.
---If the same column is used elsewhere in the SQL statement, do not alias it.
SELECT l.city, d.department_name
FROM locations l JOIN departments d
USING (location_id)
WHERE d.location_id = 1400;
----------------------------
three-way join使用:
ey:
SQL> SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
JOIN locations l
ON d.department_id = e.department_id
ON d.location_id = l.location_id;
ERROR at line 5:
ORA-00904: "E"."DEPARTMENT_ID": invalid identifier
正确写法:
SELECT employee_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;
合并查询:将多个查询的结果集进行合并得到一个全新的结果;
集合操作符:
限制:如果选择列中包含了表达式,则必须使用别名
union |union all |intersect |minus
union:自动去掉结果集中的重复行,并以第一列进行排序
union all:和union相反
intersect:求结果交集,并以第一列进行排序
minus:求结果差集,并以第一列进行排序
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23890223/viewspace-773606/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23890223/viewspace-773606/