题记：写这篇博客要主是加深自己对连接列的认识和总结实现算法时的一些验经和训教，如果有错误请指出，万分感谢。
1：在查询的时候，表中的列是属于哪张表一定要直接写上，或者用别名写上，这样会给Oracle的查询条件更加详细，oracle就减少在数据字典上消费的时光，赐与Oracle的查询条件越详细，oracle的执行效率就越高，所以不要写列名，一定要带上表名或表的别名。
2：带有表名的别名的查询中，
select后的列名可以不加表名（为了提高性能，一定要加上，尤其是写在程序中）
，
但是在where查询的条件中，必须用表的别名（用表的原表名也不正确），如果不用表的别名，会涌现错误
。
select a.employee_id ,b.department_id from employees a, departments b where employees.department_id = b.department_id;
以上sql如果运行就会报错，如果改为：
select a.employee_id ,b.department_id from employees a, departments b where A.department_id = b.department_id;
就运行畸形了。
2：关于运算的优先级：
>>>1:算术运算
1：连接查询的分类：
1：Natural Join（自然链接） ：自然连接子句道理
是 两个表中所有拥有雷同列名和列的数据类型
也雷同停止连接，
natural join ：必须两个表中有雷同的列名和列的数据类型也相等，如果两个表中两行相等，就把这两行链接起来。
- The associated tables have one or more pairs of identically named columns.
- The columns must be the same data type.
- Don’t use ON clause in a natural join.
select department_id , department_name, location_id ,city from departments natural join locations;
这两个表就链接起来了。以上是SQL标准写法。
下面利用oracle syntax 来写
select d.department_id ,d.department_name, d.location_id , l.city from departments d ,locations l where d.location_id = l.location_id;
这两个结果雷同，都是查询出来27行结果：
SQL> desc departments; 名称 是不是为空? 类型 ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> desc locations; 名称 是不是为空? 类型 ----------------------------------------- -------- ---------------------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2)
以上是两个表的结构可以看出。
1：
select employee_id ,last_name, department_id from employees natural join departments;
2：
select employee_id ,last_name, a.department_id from employees a,departments b where a.department_id = b.department_id and a.manager_id = b.manager_id;
using 只制约 列名相等便可，即使列的类型不雷同，也可以停止连接。 注意自然链接默认 链接所有的列名和数据类型相等的列：
select employee_id ,last_name,location_id,department_id from employees join departments using(department_id) ;
以上语句等同于 等同于natural join 或者 oracle 本身的句法。
注意using子句中不能应用表前缀。
可以不用指定 ON 指定列名不雷同的列停止匹配。
natural join 必须是列名和列类型全部雷同，
select employee_id ,last_name, a.department_id from employees a,departments b where a.department_id = b.department_id and a.manager_id = b.manager_id;
The result of a
left outer join
(or simply
left join
) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the
ON
clause matches 0 (zero) records in B (for a given record in A), the join will still return a row in the result (for that record)—but with NULL in each column from B. A
left outer join
returns all the values from an inner join plus all values in the left table that do not match to the right table.
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). For example, this allows us to find each employee and his or her department, but still show departments that have no employees.
5：全连接（FULL JOIN）
Conceptually, a
full outer join
combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).
CROSS JOIN returns the
of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table
Example of an explicit cross join: 显式笛卡尔集
SELECT * FROM employee CROSS JOIN department;
Example of an implicit cross join: 隐式笛卡尔集
SELECT * FROM employee, department;
- 上一篇：SQL Foundation（1--13）
- 下一篇：ASCII码排序
- 顶
- 0
- 踩
- 0
文章结束给大家分享下程序员的一些笑话语录：
小沈阳版程序员~~~ \n程序员其实可痛苦的了......需求一做一改，一个月就过去了；嚎~ \n需求再一改一调，一季度就过去了；嚎~ \n程序员最痛苦的事儿是啥，知道不？就是，程序没做完，需求又改了； \n程序员最最痛苦的事儿是啥，知道不？ 就是，系统好不容易做完了，方案全改了； \n程序员最最最痛苦的事儿是啥，知道不？ 就是，系统做完了，狗日的客户跑了； \n程序员最最最最最痛苦的事儿是啥，知道不？ 就是，狗日的客户又回来了,程序给删没了！