Review various connections in SQL statements

1. Inner connectionJoin)
An internal connection is the most common connection. Its page is called a common connection, and E. FCodd was first called a natural connection.
ANSI SQL-92 standards below
Select *
From t_institution I
InnerJoinT_teller t
On I. inst_no = t. inst_no
Where I. inst_no = "5801"
Inner can be omitted.
Equivalent to the early connection syntax
Select *
From t_institution I, t_teller t
Where I. inst_no = t. inst_no
& I. inst_no = "5801"

2. External Connection
2.1 left Outer Join (Left OuterJion)
Select *
From t_institution I
Left outer joinT_teller t
On I. inst_no = t. inst_no
WhereOuterCan be omitted.
2.2 right Outer Join (RigtOuterJion)
Select *
From t_institution I
RightOuter joinT_teller t
On I. inst_no = t. inst_no
2.3 Full outer connection (FullOuter)
All outer connections return all data in the two data sets involved in the connection, regardless of whether they have matched rows. In terms of function, it is equivalent to performing left Outer Join and right outer join on the two data sets respectively. Then, the preceding two result sets are combined into a result set by removing duplicate rows.
In real life, integrity constraints can be referenced to reduce the use of full outer connections. Generally, left outer connections are enough. When clear and standardized constraints are not used in the database to prevent erroneous data, all external connections become very useful. You can use it to clean up data in the database.
Select *
From t_institution I
FullOuter joinT_teller t
On I. inst_no = t. inst_no
2.4 use with external connections and conditions
When an inner connection query is addedJoinClause, or add to the where clause, the effect is exactly the same, but it is different for external connections. When conditions are addedJoinSQL Server and Informix return all rows of the Outer Join table, and then return the rows of the second table using the specified conditions. If conditions are placed in the where clause, SQL Server first performs the join operation, and then uses the where clause to filter connected rows. The following two Queries show the impact of conditional placement on execution results:
The condition isJoinClause
Select *
From t_institution I
Left outer joinT_teller t
On I. inst_no = t. inst_no
And I. inst_no = "5801"
The result is:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 tom
5801 Tianhe District 5801 0002 david
5802 Yuexiu District
5803 Baiyun District
Condition in where clause
Select *
From t_institution I
Left outer joinT_teller t
On I. inst_no = t. inst_no
Where I. inst_no = "5801"
The result is:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 tom
5801 Tianhe District 5801 0002 david

3. Self-connection
Self-join means that the same table is connected to itself. This type of unary join is usually used to extract data from the self-inverse (also known as recursive) relationship. For example, the relationship between employees and bosses in the HR database.
The following example shows the information of the institution and its parent institution in the organization table.
Select s. inst_no superior_inst, s. inst_name sup_inst_name, I. inst_no, I. inst_name
From t_institution I
JoinT_institution s
On I. superior_inst = s. inst_no

The result is:
Superior_inst sup_inst_name inst_no inst_name
800 Tianhe District, Guangzhou 5801
800 Yuexiu District, Guangzhou 5802
800 Guangzhou 5803 Baiyun District

4. Cross (unrestricted) connections
Crossover is used to perform multiplication of pure relational algebra on two source tables. It does not use join conditions to limit the result set, but combines rows from both data sources in all possible ways. Each row in the data set must form a new row with each row in the data set. For example, if the first data source has five rows and the second data source has four rows, 20 rows will be generated for cross-join. This type of result set is called the Cartesian product.
Most cross-connections are caused by incorrect operations, but they are very suitable for filling in the example data in the database, or creating some blank rows in advance to reserve space for the data to be filled during program execution.
Select *
From t_institution I
CrossJoinT_teller t

There is no on Condition Clause in the cross join.

Thanks http://www.cnblogs.com/xionglee/articles/1445754.html