Differences between inner join, left join, right join, and full join in SQL

I. Concepts

1. Cross join)
Without the WHERE clause, it returns the Cartesian product of the two joined tables, and the number of rows returned is equal to the product of the number of rows in the two tables.
For example:
A: select a. *, B. * From Table1 A, Table2 B where a. ID = B. ID
B: Select * From Table1 a cross join Table2 B where a. ID = B. ID
It is generally not recommended to use methods A and B, because if there is a where clause, the data table is usually formed into the data table of the rows of the two tables, and then selected based on the where condition. Therefore, if the two tables that require the intersection are too large, it will be very slow.

2. Inner join (internal join)

A combination of the two tables that meet the conditions at the same time.
For example:
Select * From Table1 a inner join Table2 B on A. ID = B. ID

If you only use the select * From Table1 inner join Table2 join, if no join condition is specified, the result of the cross join is the same as that of the Cartesian product, but unlike the Cartesian product, data tables that are less complex than cartesian products require the product of numbers in rows. The internal join efficiency is higher than that of cartesian products.

3. Left [outer] Join (left Outer Join)

Displays the rows that meet the condition, and displays the rows that do not meet the condition in the left table. No corresponding entries on the right show null.
For example:
Select * From Table1 as a left [outer] join on a. Column = B. Column

4. Right [outer] Join (right Outer Join)

Displays the data rows that meet the conditions, and the data rows that do not meet the conditions in the data table on the right. No corresponding entries on the left show null.
For example:
Select * From Table1 as a right [outer] join on a. Column = B. Column

5. Full [outer] Join (full outer join)

The data rows that meet the conditions are displayed, and the left and right data rows that do not meet the conditions are displayed. The corresponding left and right data rows are null, indicating the Union of left, right, and inner connections.
For example:
Select * From Table1 full join Table2 on table1.id = table2.id

Ii. Discrimination

> Join = Inner join
> Left join = left Outer Join
> Right join = right Outer Join
> Full join = Full outer join
> The number of records connected by a left join B is the same as that of Table.
> The number of records connected by a right join B is the same as that of Table B.
> A left join B is equivalent to B right join.
> A inner join B queries all data of A and B.
> A full outer join B queries the total data of A and B.

Iii. Example

Example Table:
Table A (A1, B1, C1) Table B (A2, B2)
A1 B1 C1 A2 B2
01 mathematics 95 01 Zhang San
02 language 90 02 Li Si
03 English 80 04 Wang Wu

1. Inner join
Select a. *, B. * from
Inner join B on (A. A1 = B. A2)
The result is:
A1 B1 C1 A2 B2
01 mathematics 95 01 Zhang San
02 language 90 02 Li Si

2. Left join
Select a. *, B. * from
Left Outer Join B on (A. A1 = B. A2)
The result is:
A1 B1 C1 A2 B2
01 mathematics 95 01 Zhang San
02 language 90 02 Li Si
03 English 80 null

3. Right join
Select a. *, B. * from
Right Outer Join B on (A. A1 = B. A2)
The result is:
A1 B1 C1 A2 B2
01 mathematics 95 01 Zhang San
02 language 90 02 Li Si
Null null 04 Wang Wu

4. Full join
Select a. *, B. * from
Full outer join B on (A. A1 = B. A2)
The result is:
A1 B1 C1 A2 B2
01 mathematics 95 01 Zhang San
02 language 90 02 Li Si
03 English 80 null
Null null 04 Wang Wu