关于Oracle数据库

4.join

1、内连接：将两个表中存在连结关系的字段符合连接条件的记录形成记录集

Select A.name,B.name from A inner join B on A.id=B.id和

Select A.name,B.name from A,B where A.id=B.id结果是一样的（内连接的inner关键字可省略）；

2、外连接：分为左外连接和右外连接

左连接A、B表结果包括A的全部记录和符合条件的B的记录。

右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：

Select A.name,B.name from A Left Join B on A.id=B.id和

Select A.name,B.name from B Right Join A on B.id-A.id执行后的结果是一样的。

3、全联结

4、无联结

5、三表联结查询

select username,psw,gname,tel from (t1 left join t2 on t1.t1_id=t2.t1_id) left join t3 on t1.t1_id=t3.t1_id

6、终极的三表联结查询

items：商品表，item_visit_stats：商品访问表，item_trade_stats：商品销售表

SELECT i.num_iid, i.title, i.price, SUM(iv.user_visits) AS uv,it.buyer_num,it.item_num,it.item_num*i.price AS turnover
FROM (items AS i RIGHT JOIN item_visit_stats AS iv ON i.num_iid=iv.num_iid)
LEFT JOIN (SELECT num_iid,SUM(buyer_num) AS buyer_num,SUM(item_num) AS item_num FROM item_trade_stats
WHERE seller_nick="XXXX" AND business_day BETWEEN '2010-08-14' AND '2010-08-15' GROUP BY num_iid)
AS it ON it.num_iid=iv.num_iid
WHERE i.nick="XXXX" AND iv.business_day BETWEEN '2010-08-14' AND '2010-08-15'
GROUP BY i.num_iid ORDER BY uv DESC

1、概述

1.1、所有的join连接，都可以加上类似where a.id='1000'的条件，达到同样的效果。

1.2、除了cross join不可以加on外，其它join连接都必须加上on关键字，后都可加where条件。

1.3、虽然都可以加where条件，但是他们只在标准连接的结果集上查找where条件。比如左外连接的结果没有class的三班，所以如果加 where class.id='C003'虽然在表中有，但在左连接结果集中没有，所以查询后，是没有记录的。

2、实例，标准的join连接，（不加where条件的）

2.1、设有表如下：

学生表

      

班级表，对应学生表中的classid

2.2、自连接：join ，inner join（又叫内连接）

1 --自连接  ：只返回两张表连接列的匹配项。
2 --以下三种查询结果一样。
3 select * from student s inner join class c on s.classid=c.id; 
4 select * from student s join class c on s.classid=c.id;
5 select * from student s,class c where s.classid=c.id;

自连接结果：

2.3、笛卡儿乘积：cross join

1 --笛卡儿乘积连接 ：即不加任何条件，达到 M*N 的结果集。
2 --以下两种查询结果一样。
3 select * from student s cross join class c;
4 select * from student,class;

笛卡尔结果：

注意：如果cross join加上where s.classid=c.id条件，会产生跟自连接一样的结果：

1 --加上条件，产生跟自连接一样的结果。
2 select * from student s cross join class c where s.classid=c.id;

自连接结果集的cross join连接结果

2.3、左外连接：left join 

1 --左连接 ：列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。（必须有where语句）
2 --在(+)计算时，哪个带(+)哪个需要条件符合的，另一个全部的。即放左即右连接，放右即左连接。
3 --以下结果集相同。
4 select * from student s left join class c on s.classid=c.id;
5 select * from student s,class c where s.classid=c.id(+);

左连接结果：

2.4、右外连接：right join

1 --右外连接 ：与左连接一样，列出右边表全部的，及左边表符合条件的，不符合条件
2 --的用 空值  替代。
3 --(+)一样，它的位置与连接相反。
4 select * from student s right join class c on s.classid=c.id;
5 select * from student s,class c where s.classid(+)=c.id;

右连接结果

2.5、全连接：full join

1 --全连接 ：产生M+N的结果集（），列出两表全部的，不符合条件的，以空值代替。（两表全部列出+列出空值，如果没有where刚好M+N行）
2 select * from student s full join class c on s.classid=c.id;

全连接结果集

3. With as

原文传送门：http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx

 

记得以前在论坛里看到inthirties用到过WITH AS这个字眼，当时没特别在意。今天在一个帖子里又看到有人用这个，所以就去网上搜了搜相关内容，自己小试了一把，写下来，方便以后忘了的话学习。

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

先举个例子吧：

有两张表，分别为A、B，求得一个字段的值先在表A中寻找，如果A表中存在数据，则输出A表的值；如果A表中不存在，则在B表中寻找，若B表中有相应记录，则输出B表的值；如果B表中也不存在，则输出"no records”字符串。

[java] view plain copy

1. with  
2. sql1 as (select to_char(a) s_name from test_tempa),  
3. sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))  
4. select * from sql1  
5. union all  
6. select * from sql2  
7. union all  
8. select 'no records' from dual  
9.        where not exists (select s_name from sql1 where rownum=1)  
10.        and not exists (select s_name from sql2 where rownum=1);  

再举个简单的例子

with a as (select * from test)

select * from a;

其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了

下面是搜索到的英文文档资料

About Oracle WITH clause
Starting in Oracle9i release 2 we see an incorporation of the SQL-99 “WITH clause”, a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL “WITH clause” is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle “WITH clause”:

   • The SQL “WITH clause” only works on Oracle 9i release 2 and beyond.
   • Formally, the “WITH clause” is called subquery factoring
   • The SQL “WITH clause” is used when a subquery is executed multiple times
   • Also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL “WITH clause” is normally used when an aggregation is referenced multiple times in a query.
We can also use the SQL-99 “WITH clause” instead of temporary tables. The Oracle SQL “WITH clause” will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH  clause”:

WITH
sum_sales AS
  select /*+ materialize */
    sum(quantity) all_sales from stores
number_stores AS
  select /*+ materialize */
    count(*) nbr_stores from stores
sales_by_store AS
  select /*+ materialize */
  store_name, sum(quantity) store_sales from
  store natural join sales
SELECT
   store_name
FROM
   store,
   sum_sales,
   number_stores,
   sales_by_store
where
   store_sales > (all_sales / nbr_stores)
;

Note the use of the Oracle undocumented “materialize” hint in the “WITH clause”. The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the “WITH” clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the “WITH clause” does not yet fully-functional within Oracle SQL and it does not yet support the use of “WITH clause” replacement for “CONNECT BY” when performing recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick’s great work “Understanding the WITH Clause” showing the use of the SQL-99 “WITH clause” to traverse a recursive bill-of-materials hierarchy
The SQL-99 “WITH clause” is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the “WITH clause” to start our SQL query, defining the aggregations, which can then be named in the main query as if they were “real” tables:

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:

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

下面自己小试一把，当然，一点都不复杂，很简单很简单的例子，呵呵。

[java] view plain copy

1. SQL> create table t2(id int);  
2.   
3. Table created.  
4.   
5. SQL> create table t3(id int);  
6.   
7. Table created.  
8.   
9. SQL> insert into t2 values(1);  
10.   
11. 1 row created.  
12.   
13. SQL> insert into t2 values(2);  
14.   
15. 1 row created.  
16.   
17. SQL> insert into t3 values(3);  
18.   
19. 1 row created.  
20.   
21. SQL> commit;  
22.   
23. Commit complete.  
24.   
25. SQL> select * from t2;  
26.   
27.         ID  
28. ----------  
29.          1  
30.          2  
31.   
32. SQL> select * from t3;  
33.   
34.         ID  
35. ----------  
36.          3  
37. SQL> with  
38.   2  sql1 as (select * from t2),  
39.   3  sql2 as (select * from t3)  
40.   4  select * from t2  
41.   5  union  
42.   6  select * from t3;  
43. sql2 as (select * from t3)  
44.                        *  
45. ERROR at line 3:  
46. ORA-32035: unreferenced query name defined in WITH clause  
47.   
48. --从这里可以看到，你定义了sql1和sql2，就得用它们哦，不然会报错的。  
49.   
50. SQL> with  
51.   2  sql1 as (select * from t2),  
52.   3  sql2 as (select * from t3)  
53.   4  select * from sql1  
54.   5  union  
55.   6  select * from sql2;  
56.   
57.         ID  
58. ----------  
59.          1  
60.          2  
61.          3  
62.   
63. --下面加个WHERE条件试试  
64.   
65. SQL> with  
66.   2  sql1 as (select * from t2),  
67.   3  sql2 as (select * from t3)  
68.   4  select * from sql1  
69.   5  union  
70.   6  select * from sql2  
71.   7  where id in(2,3);  
72.   
73.         ID  
74. ----------  
75.          1  
76.          2  
77.          3  
78.   
79. --奇怪？为什么加了WHERE条件还是输出ID=1的记录了，继续往下看：  
80.   
81. SQL> with  
82.   2  sql1 as (select * from t2),  
83.   3  sql2 as (select * from t3)  
84.   4  select * from sql1  
85.   5  where id=3  
86.   6  union  
87.   7  select * from sql2  
88.   8  where id=3;  
89.   
90.         ID  
91. ----------  
92.          3  
93.   
94. --可以看到，每个条件是要针对每个SELECT语句的。  

好了就先记这些吧，以后看到了新的用法再补充。


2.

join是连接两个表，而on是表示这两个表通过某种条件连接 

CUUG网站有Oracle免费视频教程，可以下载啦，

oracle的left join和where里的(+)怎么转换

哪边数据少哪边用 (+)

1、select  列名 from 表1 
left join  表2
on 条件
2、select  列明 from 表1 ，表2
    where 表1.条件（+）=表2.条件

追问

那这种呢？
select 表2.a as a,表2.a as b，表3.a as c，表4.b as d     from 表1 a，表2 b，表2  c，表3 d，表4 e
where a.条件（+）=b.条件
and a.条件（+）=c.条件
and d.条件（+）=c.条件
and e.条件（+）=a.条件

回答

select *  from  表1  c
left join 
(select   ss  from 表2 a
left join  表3 b
on a.ss=b.ss) f
on  f.dd = c.dd

后面的自己写

1. <> 和 null

今天碰到一个Oracle不等于的问题，最后搜索了一下，发现下面资料，拿来跟大家分享一下

 

关于Oracle中的不等于号：

在Oracle中，

<>
!=
~=
^=

都是不等于号的意思。都可以使用。

但是奇怪是的， 我想拿出price不是180000的商品时：（price是Number类型的）

SELECT id, name  FROM product where price<> 180000;

执行这个语句时，priceis null  的记录不出来。也就是拿不到price是null的商品。必须使用：

SELECT id, name  FROM product where price<> 180000 or price is null;才行。

字符串的字段存在同样的问题。

记住：null只能通过is null或者is not null来判断，其它操作符与null操作都是false。

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

   测试：select * from test where name<>'xn'。只能查出name非空的记录。去掉name<>'xn'就可以了。这种写法有问题。

       然后用了instr(name,'xn')=0 来判断，如果name非空的话，判断还是有效的。如果name为空，这个判断又出问题了。不得已只得采取instr(concat(name,'xx'),'xn') = 0来判断，因为就算name为空，当和'xx'连接后，也会不为空的。

       所以最后的sql语句为：

       select * from test where instr(concat(name,'xx'),'xn') = 0 来查询name字段不等于'xn'的记录。

       或者可以用 select * from test where nvl(name,'xx')<>'xn' 来查询name字段不等于'xn'的记录。