SQL Foundation（14--17）

不错SQL join讲解

WIKI JOIN

1：关于表的别名说明：

         1：在查询的时候，表中的列是属于哪张表一定要直接写上，或者用别名写上，这样会给Oracle的查询条件更加详细，oracle就减少在数据字典上消耗的时间，给予Oracle的查询条件越详细，oracle的执行效率就越高，所以不要写列名，一定要带上表名或表的别名。

       2：带有表名的别名的查询中，select后的列名可以不加表名（为了提高性能，一定要加上，尤其是写在程序中），

但是在where查询的条件中，必须用表的别名（用表的原表名也不正确），如果不用表的别名，会出现错误。

ex：

    

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:算术运算

            >>>>2:连接运算

            >>>>3:比较运算

            >>>>4: IS [NOT]  , LIKE ,[NOT] IN

            >>>>5: [NOT]  BETWEEN

            >>>>6:not equal to

            >>>>7: not 逻辑条件

            >>>>8:  and 逻辑条件

            >>>>9:OR   逻辑条件

3：select 语法

4：连接查询

    1：连接查询的分类：

    

                        1：Natural Join（自然链接） ：自然连接子句原理 是 两个表中所有具有相同列名和列的数据类型也相同进行连接， 自然连接（WIKI解释）

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.

EX: 

 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行结果：

之所以两个结果相同， 因为 在departments 和 locations表中两个表只有一列具有相同 的列名和列类型：

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)

以上是两个表的结构可以看出。

以下两个sql语句的效果一样：

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;

2：using连接：

natural join 和using 是互斥的，不可以同时使用，using 比 natural join 优越的地方是：

     using 只限制 列名相等即可，即使列的类型不相同，也可以进行连接。  注意自然链接默认 链接所有的列名和数据类型相等的列：

ex：

 

 select employee_id ,last_name,location_id,department_id
 from employees join departments using(department_id)
 ;

以上语句等同于 等同于natural join 或者 oracle 本身的句法。

注意using子句中不能使用表前缀。 

3： on 子句

可以不用指定 ON 指定列名不相同的列进行匹配。 

这三个关键子句依次放宽执行条件：

       natural join  必须是列名和列类型全部相同，

       using 子句   列名必须相同，列类型可以不同。

       on 子句     列名和列类型都可以不相同。

上图体现了多个 表利用on相连接 。

======================================================================================================

以上讲解的都是标准的SQL语句执行。

SQL标准定义的相当扯淡，还是Oracle自定义的用着比较爽。对SQL标准制定者表示鄙视。以下是标准Oracle写法，不用考虑任何其他的问题（PS列名是否相等，是否列类型相同）

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;

运用on 连接 进行条件过滤  ，可以用 and 过滤 也可以用 where过滤，如下图：

3：内连接：

在SQL1999标准中规定，两个连接返回仅仅相匹配的行叫做内连接

4：外连接

左外连接(LETT OUT JOIN)：

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.

右外连接(RIGHT OUT JOIN)：

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).

5：笛卡尔集

CROSS JOIN returns the Cartesian product 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

笛卡尔集返回两个表中乘积的所有数据，

比如 A 表有 8行数据， B 表有 9行数据

笛卡尔集返回 72行数据，

一般情况下不用笛卡尔集，如果需要制造大批量，海量数据的时候可以用多个表进行笛卡尔集，制造测试数据是笛卡尔集的一个最大的好处。

Example of an explicit cross join: 显式笛卡尔集

SELECT *
FROM employee CROSS JOIN department;

Example of an implicit cross join: 隐式笛卡尔集

SELECT *
FROM employee, department;