Join join in SQL

Inner join on, full outer join, left join on, right jion on
1. Inner join on combination of internal join tables
2. full outer is connected to the same combination of the two tables. Table A has data that table B does not have (it is displayed as null), table B has data, and table A does not show (null)
3. Table A left join table B's left join, which is based on table A. All data in Table A and B's combination. Null is not found.
4. Table A right join table B, which is based on table B. All data in Table B and some combinations in table. Null is not found.

 

1. Inner join on or join
As long as the public fields of the two tables have matched values, the records in the two tables are combined.
Use a common field to calculate the intersection of the two tables that meet the requirements, and combine the records that meet the requirements of each table with the common fields. In fact, it is to take their intersection. The inner join is to use a comparison operator to compare the join of the values of the columns to be joined.

Syntax
From Table1 inner join Table2 on Table1. field1 compopr Table2. field2

The inner join operation includes the following parts:
Description
 
The name of the table in which Table1 and Table2 are to be combined.
The name of the field to be joined by field1 and field2. If they are not numbers, these fields must have the same data type and contain similar data, but they do not have to have the same name.
Compopr
Comparison operators for any link: "=", "<", ">", "<=", "> =", or "<> ".

SQL statement
Select * From Table1 join Table2 on table1.id = table2.id
Equivalent (same as the following execution)
A: select a. *, B. * From Table1 A, Table2 B where a. ID = B. ID
B: Select * From Table1 cross join Table2 where table1.id = table2.id (Note: cross join can only use where, not on)

Description
1. The inner join operation can be used in any from clause. This is the most common connection type. As long as the public fields of the two tables have matched values, the inner join will combine the records in these tables.
2. Inner join can be used for the Departments and employees tables to select all employees in each department. Select All parts (even if some departments are not assigned employees) or all employees (even if some employees are not assigned

Any department), you can use the left join or right join operation to create an external join.

3. If you try to join fields that contain remarks or OLE object data, an error will occur.

4. You can join any two numeric fields of similar types. For example, automatic numbers and long integer fields can be joined because they are of similar type. However, single-precision and double-precision fields cannot be connected.

The following example shows how to use the categoryid field to join the categories and products tables:

Select categoryname, productname

From categories inner join Products

On categories. categoryid = products. categoryid;

In the previous example, categoryid is a joined field, but it is not included in the query output because it is not included in the SELECT statement. To include a joined field, include the field name in the SELECT statement.

Categories. categoryid.

You can also link Multiple on clauses in a join statement. Use the following syntax:

Select fields
From Table1 inner join Table2
On table1.field1 compopr table2.field1 and
On table1.field2 compopr table2.field2) or
On table1.field3 compopr table2.field3)];

You can also use the following syntax to nest join statements:

Select fields
From Table1 inner join
(Table2 inner join [(] table3
[Inner join [(] tablex [inner join...)]
On table3.field3 compopr tablex. fieldx)]
On table2.field2 compopr table3.field3)
On table1.field1 compopr table2.field2;

Note: left join or right join can be nested in inner join, but inner join cannot be nested in left join or right join.

Ii. External Connection
1. Concept: including left Outer Join, right Outer Join or complete external join

2. Left join: left join or left Outer Join
(1) The result set of the left Outer Join includes all rows in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection list columns in the right table in the row of the associated result set

All are null values ).
(2) SQL statements
Select * From Table1 left join Table2 on table1.id = table2.id

Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

3. Right join: Right join or right Outer Join
(1) The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.
(2) SQL statements
Select * From Table1 right join Table2 on table1.id = table2.id

Note: all the clauses containing Table 2 return the corresponding fields of Table 1 Based on the specified conditions. The non-conforming fields are displayed as null.

4. Complete External join: Full join or full outer join
(1) The Complete External join returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.
(2) SQL statements
Select * From Table1 full join Table2 on table1.id = table2.id

Note: returns the sum of left and right connections (see upper left and right connections)

 

Iii. Cross-join (complete)

1. Concept: A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table1 and Table2 generate 3*3 = 9 records)
2. Cross join: cross join (without the condition where ...)
3. equivalent (same as the following execution)
A: Select * From Table1, Table2

 

4. Operate instances

Table A records the following:
Aid anum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

Table B records the following:
Bid bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

 

The experiment is as follows:
1. Left join

The SQL statement is as follows:
Select * from
Left join B
On a. Aid = B. Bid

The result is as follows:
Aid anum bid bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 null
(The number of affected rows is 5)

Result description:
Left join is based on the records of table A. A can be seen as the left table, B can be seen as the right table, and left join is based on the left table.
In other words, the records in the left table (a) are all expressed, while the right table (B) only displays records that meet the search criteria (in this example:. aid = B. bid ).
All records in Table B are null.

 

 

2. Right join
The SQL statement is as follows:
Select * from
Right join B
On a. Aid = B. Bid
The result is as follows:
Aid anum bid bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
Null null 8 2006032408
(The number of affected rows is 5)
Result description:
After careful observation, we will find that the result of left join is exactly the opposite. This time, it is based on the right table (B) and is filled with null when table A is insufficient.

3. Inner join
The SQL statement is as follows:
Select * from
Innerjoin B
On a. Aid = B. Bid

The result is as follows:
Aid anum bid bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404

Result description:
Obviously, only the records of A. Aid = B. Bid are displayed here. This indicates that inner join is not based on WHO, and only records meeting the conditions are displayed.