Left outer join,inner join,right outer join usage in SQL

These two days, in the study of SQL syntax in the use of inner JOIN multi-table query syntax, through learning, found a SQL command, actually involved in a lot of linear algebra knowledge, this knowledge is now systematically recorded as follows:

Merging data using relational algebra
1 Relational algebra
The theoretical basis for merging data sets is relational algebra, which was proposed by E.f.codd in 1970.
In the formal language of relational algebra:
? Represents a relationship or entity with a table, or data collection.
? Represents a tuple in rows.
? Represents a property with a column.
The relational algebra consists of the following 8 relational operators
? Select-Returns the rows that meet the specified criteria.
? Drop shadow-Returns the specified column from the data collection.
? Cartesian product-is the multiplication of relationships, which combine rows from two data sets in all possible ways.
? And--the addition and subtraction of relationships, which can combine data from two tables in the direction of a row, just like putting one table on top of another.
? Returns the rows that are common to two data sets.
? Bad-returns rows that belong to only one data collection.
? Join-Merges two tables horizontally, by merging those rows in two tables that match each other on a common data item.
? Except--Returns an exact match between two datasets.
In addition, as a way to implement modern relational algebra operations, SQL provides:
? Subqueries-similar to connections, but more flexible; In an external query, the result of a subquery can be used where the expression, list, or data collection is used.
This chapter will focus on several types of joins, simple and correlated subqueries, several types of and, relationships, and other content.
2 Using the connection
2.1 Connection Type
In relational algebra, a join operation is composed of a Cartesian product operation and a selection operation. First, a Cartesian product is used to complete the multiplication of two data sets, and then the resulting set of results is selected to ensure that only rows from two data sets with overlapping parts are merged together. The whole point of the connection is to merge two data sets (usually tables) horizontally, and produce a new result set by combining rows from one data source with rows that match it into a single new tuple.
SQL provides several types of connection methods, the difference being that the method used to select the rows to connect from each overlapping set of data is different.
Connection type definition
An inner join connects only matching rows
The left OUTER join contains all rows from the table on the left (regardless of whether there are rows in the right table that match them), and all rows in the right table
The right outer join contains all rows from the right table (regardless of whether there are rows in the left table that match them), and all rows in the left table
A full outer join contains all rows from the left and right two tables, regardless of whether there are rows in the other side of the table that match them.
H (theta) connection uses a condition other than equivalence to match rows in the left and right two tables
Cross join generates Cartesian product-it does not use any matching or selection criteria, but instead directly matches each row in one data source with each row of another data source
Query for connecting tables in Informix
If more than one table reference is specified by the FROM clause, the query joins rows from more than one table. A join condition specifies the relationship between columns (at least one column per table). Because the columns in the join condition are being compared, they must have a consistent data type.
The FROM clause of the SELECT statement can specify the following types of connections
Result set corresponding to the FROM clause keyword
CrossJOINCartesian product (all possible pairs of rows)
INNERJOINColumns in cross that meet the join condition only
LeftOUTERJOINOne table satisfies the condition of the row, and all rows of the other table
RightOUTERJOINAnd LeftSame, but two-table role swaps
FullOUTERJOIN LeftOUTERAnd rightOUTERSuperset of all rows in

2.2 Internal connection (InnerJoin）
Inner joins are the most common kind of connection, and the page is called a normal connection, and E.fcodd is the earliest known as a natural connection.
The following is the ANSI SQL-92 standard
SELECT *
From T_institution I
InnerJoinT_teller T
On i.inst_no = T.inst_no
where i.inst_no = "5801"
Where 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
and i.inst_no = "5801"

2.3 External Connection
2.3.1 Left Outer connection ( LeftOuterJion)
SELECT *
From T_institution I
LeftouterJoinT_teller T
On i.inst_no = T.inst_no
whichoutercan be omitted.
2.3.2 Right outer connection (rigtOuterJion)
SELECT *
From T_institution I
RightouterJoinT_teller T
On i.inst_no = T.inst_no
2.3.3 Full-Outer connectionOuter)
The full outer join returns all the data in the two data sets that participate in the connection, regardless of whether they have rows that match them. Functionally, it is equivalent to making a left outer and right outer joins on the two data sets, and then merging the two result sets into a single result set by eliminating duplicate rows and manipulating them.
In real life, referential integrity constraints can reduce the use of all-out connections, and in general, left outer joins are sufficient. In a database where clear, prescriptive constraints are not used to protect against erroneous data, an all-out connection becomes very useful, and you can use it to clean up data in the database.
SELECT *
From T_institution I
FullouterJoinT_teller T
On i.inst_no = T.inst_no
2.3.4 external connections and conditions used together
When a condition is added to the in-connection query, either add it to theJoinclause, or join the WHERE clause, the effect is exactly the same, but the outer joins are different. When the conditions are added to theJoinclause, SQL Server, Informix returns all rows of the outer join table, and then returns the rows of the second table using the specified criteria. If you place the condition in the WHERE clause, SQL Server will first make a connection operation, and then use the WHERE clause to filter the concatenated rows. The following two queries show the effect of conditional placement of a seat on the execution result:
Conditions inJoinClause
SELECT *
From T_institution I
LeftouterJoinT_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 the WHERE clause
SELECT *
From T_institution I
LeftouterJoinT_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

2.4 Self-connection
Self-connection refers to the same table itself connected to itself. This unary join is typically used to extract data from a reflexive relationship (also known as a recursive relationship). For example, the relationship between employees and bosses in a human resources database.
The following example finds information about this institution and the parent organization 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 Guangzhou city 5801 Tianhe District
800 Guangzhou City 5802 Yuexiu District
800 Guangzhou City 5803 Baiyun District

2.5 Cross (unlimited) connection
A cross join is used to multiply the pure relational algebra of two source tables. Instead of restricting the result collection with join conditions, it combines rows from two data sources in all possible ways. Each row of one in the data collection is made into a new row with each row in the data collection two. For example, if there are 5 rows in the first data source and 4 rows in the second data source, then cross-joins between them will result in 20 rows. People call this type of result set a Cartesian product.
Most cross-connections are caused by error operations, but they are ideal for populating the database with sample data, or pre-creating empty rows to reserve space for the data to be populated during program execution.
SELECT *
From T_institution I
CrossJoinT_teller T
No on conditional clauses in cross joins
Through the above knowledge, is really a systematic study, found that inner join can actually be achieved through the initial multi-table Query method, for example:

SELECT * from T_institution I, T_teller t where I.inst_no = t.inst_no and i.inst_no = "5801"

In fact, inner join is a solution to a multi-table query. The outer connection, or has its specific usefulness, is actually equivalent to an open interval, and the inner connection is a closed interval.

Left outer join,inner join,right outer join usage in SQL