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