Inner join on, left join on, right join on, innerjoin

Inner join on, left join on, right join on, innerjoin

1. Definition:

Inner join: only records with equal join fields in two tables are returned.

Left join: returns records that include all records in the left table and join fields in the right table.

Right join: returns all records in the right table that are equal to the join fields in the left table.

 

Inner join Syntax:

Use inner join to JOIN two data tables:

SELECT * FROM table 1 inner join table 2 ON table 1. Field number = TABLE 2. Field number

2. Instance

Table A records the following:
AID a Num
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 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. aID = B. bID record. this indicates that inner join is not based on WHO, and only displays records that meet the conditions. in addition, inner join can be used in combination with where statements, for example: select * from A innerjoin B on. aID = B. bID where B. bname = '000000'. In this way, only one data record is returned.