SQL left Outer Join, inner join, right Outer Join usage (left and right inner join)

SQL statement left Outer Join, inner join, right Outer Join usage

Left Outer Join = left join, right Outer Join = right join, inner join = join.
Merge data using relational algebra
1 relational algebra
The theoretical basis of Data Set merging is relational algebra, which was proposed by E. F. codd in 1970.
In the formal language of relational algebra:
Tables or data sets are used to represent links or entities.
Use rows to represent tuples.
Columns are used to represent attributes.
Relational algebra contains the following eight Relational operators
Select to return the rows that meet the specified conditions.

Projection: returns the specified column from the dataset.
Cartesian product-is the multiplication of relations. It combines rows from two datasets in all possible ways.
And -- addition and subtraction of the relationship. It can merge the data in two tables in the row direction, just like putting one table on another table.

-- Returns the rows in the two data sets.
Difference-return rows that belong to only one data set.
Join-merge two tables horizontally by merging the rows matching each other on the common data items in the two tables.
In addition, the exact match between two datasets is returned.
In addition, as a method for implementing modern relational algebra operations, SQL also provides:
Subqueries-similar to connections, but more flexible; in external queries, the results of subqueries can be used in expressions, lists, or data sets.
This chapter describes multiple types of connections, simple and related subqueries, and the types of joins, links, and other content.
2 Use connection
2.1 connection type
In relational algebra, join operations are composed of a Cartesian Product operation and a selection operation. First, use Cartesian product to perform multiplication on two data sets, and then select the generated result set, make sure that only the rows from two data sets with overlapping parts are merged. The full meaning of join is to merge two data sets (usually tables) horizontally and generate a new result set, the method is to combine the rows in one data source with the matched rows in another data source into a new Meta Group.
SQL provides multiple types of connections. The difference between them is that the method used to select rows for connection is different from different overlapping data sets.
Connection Type Definition
The inner connection only connects the matched rows.
The left Outer Join contains all rows in the left table (no matter whether the table on the right has rows matching them) and all matched rows in the right table
The right Outer Join contains all rows in the right table (no matter whether the left table has rows matching them) and all matched rows in the left table
The full outer join contains all rows in the left and right tables, regardless of whether the tables on the other side have rows matching them.
(H) (theta) join uses conditions other than equivalent to match rows in the left and right tables
Cross join generates Cartesian Product-instead of using any matching or selection conditions, it directly matches each row in a data source with each row in another data source.
Join Table query in Informix
If the from clause specifies more than one table reference, the query connects rows from multiple tables. The connection condition specifies the connection relationship between each column (at least one column in each table. Because columns in the join condition are being compared, they must have consistent data types.
The from clause of the SELECT statement can specify the following types of connections:
Result set corresponding to the from clause keyword
Cross join Cartesian Product (all possible row pairs)
Inner join Only columns in cross that meet the connection conditions
Left Outer Join Rows in one table that meet the conditions, and all rows in the other table
Right outer Join And left Same, but two tables have different roles.
Full outer Join Left Outer And right outer Superset of all rows in

Inner join)
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
Inner joinT_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.3 outer connections
2.3.1 left Outer Join (left Outer Jion)
Select *
From t_institution I
Left Outer Join T_teller t
On I. inst_no = T. inst_no
Outer Can be omitted.
2.3.2 right Outer Join (rigt outer Jion)
Select *
From t_institution I
Right outer Join T_teller t
On I. inst_no = T. inst_no
2.3.3 full outer connection )
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
Full outer Join T_teller t
On I. inst_no = T. inst_no
2.3.4 use with external connections and conditions
When a join query is added to an internal join query Clause, or add to the WHERE clause, the effect is exactly the same, but it is different for external connections. When conditions are added to join SQL 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:
Condition in join Clause
Select *
From t_institution I
Left Outer Join T_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 Join T_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-join
A 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 information about 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
join t_institution S
on I. superior_inst = S. inst_no

result:
superior_inst sup_inst_name inst_no inst_name
800 Guangzhou 5801 Tianhe District
800 Guangzhou 5802 Yuexiu District
800 Guangzhou 5803 Baiyun district

2.5 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 the example data in the database, or creating some blank rows in advanceProgramReserved space for data to be filled during execution.
Select *
From t_institution I
Cross joinT_teller t
There is no on Condition Clause in the cross join.

Inner join
Inner join should be the most common join method. It will only return records that comply with the join rules. Let's take a look at the syntax first.
Select <field to be selected> from <main data table>
<Join method> <secondary data table> [on <join rule>]
Select productid, productname, suppliers. supplierid
From Products
Inner join suppliers
Products. Suppliers = suppliers. supplierid
The main spirit of inner join is exclusive. Call it exclusive! That is, data that does not match the join rule will be excluded,
For example, there is a product supplier in the product. Code (Supplierid) is not displayed in the suppliers data table, so this record will be excluded
Outer Join
This join method is rarely used by ordinary people, and even some SQL managers have never used it. This is really a sad ambition, because using outer join can simplify some query operations, let's take a look at the SQL statements and syntax of Outer Join.
Select <field to be queried> from <left table>
<Left | right> [outer] Join <right table> On <join rule>
Outer In the syntax can be omitted. For example, you can use left join or right join. In essence, outer join is inclusive! Different from the exclusive nature of inner join, the query results in left Outer Join will contain information about all left tables. If you look back, right outer join queries will contain information about all right tables.
Conclusion: inner join is an internal join and outer join is an external join. Inner calculates the intersection. Outer indicates all rows in a table, regardless of whether the other table has this row.
Inner join code as the following:
Select * From A, B where a. categoryid = B. categoryid;
Equals:
Select * from a inner join B on A. categoryid = B. categoryid;
Outer Join code as the following
Select * from a full (left/right) Outer Join B on a on A. categoryid = B. categoryid;
Left/right Outer Join Claus specific for MSSQL:
Select * From A, B where a. categoryid * = bcategoryid;
Elect * From A, B where a. categoryid = * B. categoryid;
Left/right Outer Join Claus specific for Oracle:
Select * From A, B where a. categoryid = B. categoryid (+ );
Select * From A, B where a. categoryid (+) = B. categoryid;

 

A final thing worth discussing here is the use of aliases. aliases aren't necessarily related to table joins, but they become especially useful (and sometimes seconds) when joining tables, and they assign different (and usually shorter) names for the tables later. aliases are necessary when joining a table with itself, in which case you need to assign different aliases for its different instances to differentiate them. the following query returns the same products as the query before, but it uses aliases:

Select P. productid, P. Name
From productcategory PC inner join product P
On P. productid = pc. productid
Where PC. categoryid = 5