★★★oracle outer joins, the difference between left Outer join and Outer Association (+) in Oracle __linux

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  tota Lmoney
from  table1 a (nolock)
left   JOIN  table3 c (nolock)   on  a. [UserID] = c. [UserID],   table2 b (nolock)  

WHERE   a. [UserID]   =  b. [User ID]   and  a. [Time]   >=   ' 2005-01-01 '   and  a. [Time]   <=   ' 20 06-12-31 '  

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

Order   by  a. [Time] &nbs P DESC  

SELECT * FROM
Table1 LEFT join table2 on condition 1
Left join Table3 on condition 2
Left join Table4 on condition 3
Where Condition 4

GROUP by Description:
GROUP BY

You can use the GROUP BY clause to divide rows into smaller groups in a SELECT statement, and then use the cluster function to return summary information for each group, and you can use the HAVING clause to restrict the returned result set. The GROUP BY clause can group query results and return the summary information for the row. Oracle groups 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 or the cluster function

Select Max (SAL), Job emp Group by job;
(Note that Max (SAL), job job does not necessarily appear, but meaningful)

The select and group by of a query statement, 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 the EMP where sal>1200 GROUP by Deptno has sum (SAL) >8500 order by Deptno;

When a HAVING clause is used in a gropu by clause, only the group that satisfies the having condition is returned in the query result. You can have a WHERE clause and a HAVING clause in an SQL statement. Having is similar to a WHERE clause and is used to set qualifications

The role of the WHERE clause is to remove rows that do not conform to the where condition before grouping the query results, that is, to filter the data before grouping, in conditions that cannot contain a clustered function, and where conditions are used to display a particular row.
The HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including the clustering function, using the having condition to display a specific group, or multiple grouping criteria for grouping.

Inquire about the number of employees per position in each department
Select Deptno,job,count (*) from the EMP group by Deptno,job;

Oracle Table Connection Method (2008-08-06-16:29:54)

Http://blog.sina.com.cn/s/blog_4cfcdbf00100a5ri.html