Oracle Connection Query

one, inner and outer connections are used to return records that meet the conditions of the connection, and the outside connection is an extension of the inner join, which not only satisfies the record of the join condition, but also returns records that do not meet the conditions of the join, as follows:

Oracle Code

1. Select Table1.column,table2.column from table1 [inner|left|right|full]join table2 on Table1.column=table2.column;

The inner join represents an inner join, a LEFT join that represents an outer join, a right join that represents an outer join, a full join representing an entire connection, and on to specify a connection condition. Note: You must use the on operator to specify the join condition if you are using a form inside or outside connection, and if you use the (+) operator, you must specify a connection condition using the where. 1 . Internal connection query returns all records that meet the criteria, and no connection is specified by default for internal connections, for example:

Oracle Code

1. Select T1.name,t2.name from Cip_temps t1 inner joins cip_tmp T2 on T1.id=t2.id;

2 . LEFT outer JOIN LEFT OUTER JOIN query not only returns all records that satisfy the condition, but also returns other rows of the table to the left of the join operator that do not meet the join condition, for example:

Oracle Code

1. Select T1.name,t2.name from Cip_temps T1 left joins Cip_tmp T2 on T1.id=t2.id;

3 . Right outer join right OUTER JOIN query not only returns all records that satisfy the price adjustment, but also returns other rows of the table to the right of the join operator that do not meet the join condition, for example:

Oracle Code

1. Select T1.name,t2.name from Cip_temps T1 right joins cip_tmp T2 on T1.id=t2.id;

4. Fully connected fully connected query not only returns all records that satisfy the price adjustment, but also returns other rows that do not meet the join criteria, for example:

Oracle Code

1. Select T1.name,t2.name from cip_temps T1 full join cip_tmp T2 on T1.id=t2.id;

5, (+) operator before Oracle9i, when performing an outer join, is done using the connection operator (+), although you can use the operator (+) to perform an outer join operation, but oracle9i start Oracle recommends the use of outer join to perform an outer join , the syntax for performing an outer join using the (+) operator is as follows:

Oracle Code

1. Select Table1.column,table2.column from Table1,table2 where Table1.column (+) =table2.column;

Attention:

• When an outer join is performed using the (+) operator, the operator should be placed at one end of the display of fewer rows (fully satisfying the join condition row).
• The (+) operator can only appear in the WHERE clause and cannot be used in conjunction with the outer join syntax.
• When you use the (+) operator to perform an outer join, if you include more than one condition in the WHERE statement, you must include the (+) operator in all conditions.
• The (+) operator applies only to columns, not to expressions.
• The (+) operator cannot be used with the OR and in operators.
• The (+) operator can only be used for left and right outer joins, and cannot be used to implement full connections.

(1), using the (+) operator to perform a left outer join when using a left outer join, not only will all rows that satisfy the join condition be returned, but other rows that do not satisfy the left side of the join condition will also be returned. Because the (+) operator is placed at one end of a small number of rows, the operator should be placed at one end of the right table in the WHERE clause, as in the following example:

Oracle Code

1. Select T1.name,t2.name from Cip_temps t1,cip_tmp T2 where t1.id=t2.id (+);

(2), using the (+) operator to perform a right outer join when using a right outer join, not only will all rows that satisfy the join condition be returned, but other rows that do not meet the right side of the join condition will also be returned. Because the (+) operator is placed at one end of a small number of rows, the operator should be placed at one end of the left table in the WHERE clause, as in the following example:

Oracle Code

Select T1.name,t2.name from Cip_temps t1,cip_tmp T2 where t1.id (+) =t2.id;

Original http://shawnfree.iteye.com/blog/355837

Oracle Connection Query