The difference between left Outer join and Outer Association (+) in Oracle ____oracle

Difference between left Outer join and Outer Association (+) in "original" Oracle 2008-03-23 16:22:37

url:http://space.itpub.net/?uid-6517-action-viewspace-itemid-216974

difference between on and where in Oracle's left join 2009-09-28 15:20

Url:http://hi.baidu.com/bfsll/blog/item/3a884e2f0fc905321e3089a7.html

Today we have a statistical result that asks for all the days of a month, and if the result of one day is 0, it needs to be shown, namely:

Date Transaction Number Transaction amount

2009-4-01 1 10

2009-4-02 2 20

2009-4-03 0 0

2009-4-04 5 50

....

At first I used the left connection, using on as a two-table association condition, where as a filter condition, but found that 0 of the data does not show, and then remove the Where keyword, the filter conditions are put on, the problem solved, online search, found the answer:

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and then the temporary table is returned to the user.

When you use the left jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the table on the left regardless of whether the condition in on is true.

2. Where condition is the condition of filtering the temporary table after the temporary table is generated. There is no meaning for the left join (the record must be returned to the left-hand table), and the condition is not really filtered out.

Suppose there are two tables:

Table 1 TAB1:

ID size

1 10

2 20

3 30

Table 2 TAB2:

Size Name

Ten AAA

BBB

CCC

Two sql:
1, select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size) where tab2.name= ' AAA '
2, select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size and Tab2.name= ' AAA ')

The first SQL process:

1. Middle table
On condition:
Tab1.size = Tab2.size

Tab1.id tab1.size tab2.size Tab2.name

1 AAA

2 BBB

2 CCC

3 (NULL) (NULL)

2. Filter the middle table again
Where Condition:
Tab2.name= ' AAA '

Tab1.id tab1.size tab2.size Tab2.name

1 AAA

Process of the second SQL:

1. Middle table
On condition:
Tab1.size = tab2.size and tab2.name= ' AAA '
(The condition is not true and the record in the left table is returned)

Tab1.id tab1.size tab2.size Tab2.name

1 AAA

2 (NULL) (NULL)

3 (NULL) (NULL)

In fact, the key reason for the above result is the particularity of the left Join,right Join,full join, which returns records in the left or right table regardless of whether the condition on the on is true , and full has the set of left and right attributes. Inner Jion does not have this specificity, the condition is placed in on and where the result set returned is the same.

Turn from: Http://hi.baidu.com/%BA%D3%B1%B1%B6%FE%C9%D9/blog

multiple table links left join

Url:http://www.cnblogs.com/windamy/articles/585555.html

A single Instance I wrote: Multiple table connections, with 3 tables connected altogether. Using the aggregate function sum, the group by SELECT  a is used. [UserID], B. [Name], sum   (c. [Money] + C. [Bank]) as  totalmone Y
from  table1 a (nolock)
  left   JOIN  table2 b (nolock)   on  a. [UserID ]   =  b. [UserID]  
Left   JOIN  table3 c (nolock)   on  b. [UserID] = c. [ UserID]  

WHERE   a. [UserID]   =  b. [UserID]   and  a. [UserID]   ; =  c. [UserID]   and  a. [Time]   >=   ' 2005-01-01 '   and  a. [Time]   <=   ' 2006-12-31 '  

GROUP   by  a. [UserID], B. [Name]

Order   by   ; a. [Time]   DESC  

tuning

=========================================================================== =====
Left Join syntax:
SELECT  a. [UserID], B. [Name], sum   (c. [Money] + C. [Bank]) as  tot Almoney
from  table1 a (nolock)
left   JOIN  table3 c (nolock)   on  a. [UserID = c. [UserID],   table2 b (nolock)  

WHERE   a. [UserID]   =  b. [U Serid]   and  a. [Time]   >=   ' 2005-01-01 '   and  a. [Time]   <=   ' 2006-12-31 '  

GROUP   by  a. [UserID], B. [Name]

Order   by  a. [Time] & nbsp DESC  

  Select     *     from
table1    left     join    table2    on     condition 1   
Left     Join    table3    on     condition 2   
Left      Join    table4    on     condition 3  
where      Conditions 4

GROUP by Description:
group by 

     can use Group by in select  statements The   clause divides the rows into smaller groups, and then uses the cluster function to return the summary information for each group, and you can use the HAVING clause to restrict the returned result set. The group by  clause groups the query results and returns the summary information for the rows oracle  the query results by the value of the expression specified in the group by  clause.

    in a query statement with a group by  clause, the column specified in the select  list is either the column specified in the group by  clause, Either include the cluster function

   select max (sal), Job emp group by job
    (note Max (SAL), job job does not necessarily appear, but meaningful)

    select  and group by of query statements The  ,having  clause is the only place where the cluster function appears, and the cluster function cannot be used in the where  clause.

  select deptno,sum (SAL)  from emp where sal>1200 group  by deptno having sum (SAL) >8500 order by deptno;

   When the having  clause is used in the gropu by  clause, only groups that satisfy the having condition are returned in the query results. You can have a WHERE clause and a HAVING clause in an SQL statement. having  is similar to the where  clause and is used to set the qualification