Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right table
Right join returns records that include all records in the right table and the junction fields in the left table
INNER JOIN (equivalent join) returns only rows that have the same join field in two tables
Examples are as follows:
--------------------------------------------
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
--------------------------------------------
1.left Join
The SQL statements are as follows:
SELECT * FROM A
Left JOIN B
On a.aid = B.bid
The results are 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 NULL
(The number of rows affected is 5 rows)
Result Description:
The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table.
In other words, the records of the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
The low-record of table B is null.
--------------------------------------------
2.right Join
The SQL statements are as follows:
SELECT * FROM A
Right Join B
On a.aid = B.bid
The results are 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 rows affected is 5 rows)
Result Description:
Looking closely, you will find that the result of the left join is exactly the opposite, this time it is based on the right table (B), where a table is not enough to fill with null.
--------------------------------------------
3.inner Join
The SQL statements are as follows:
SELECT * FROM A
Innerjoin B
On a.aid = B.bid
The results are 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 a.aid = B.bid records are shown here. This shows that inner join is not based on who, it only shows records that match the criteria.
When paired with a where statement:
When using the left JOIN,
Conditions are placed directly on the back, which is first filtered and then connected,
sql statement as follows:
Select * from A
Left join b
on a.aid = B.bid and b.bid = 1
The results are as follows :
AID anum bID bname
1 a20050111 1 2006032401
2 a20050112 null NULL
3 a20050113 null NULL
4 a20050114 null null
5 a20050115 null null
when the condition is placed behind, the filter is first connected,
The SQL statements are as follows:
SELECT * FROM A
Left JOIN B
On a.aid = b.bid
where b.bid=1
The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
Other connection queries similarly
Differences between SQL left JOIN, right join, and inner join and where to use