The difference between the left join and the right join in the database

Left Join/right Join/inner Join related

A concluding remark about left and right connections:

Left join the Where only shadow right table, right join where only affects the left table.

Left Join

SELECT * from tbl1 left Join tbl2 where tbl1.id = Tbl2.id

The left join result is a display of all data tbl1 and data that satisfies the where condition in tbl2.

In short, the left join affects the table on the right.

Right Join

SELECT * from Tbl1 right Join tbl2 where tbl1.id = Tbl2.id

The result is tbl2 all data and data that satisfies the where condition in the TBL1.

In short, right joins affect the table on the left.

INNER JOIN

SELECT * from Tbl1 INNER JOIN tbl2 on tbl1.id = tbl2.id

The function and select * from TBL1,TBL2 where tbl1.id=tbl2.id are the same.

Other relevant information

1. The connection statement used in the WHERE clause, in the database language, is called the implicit connection. INNER JOIN ... The connection generated by the ON clause is called a dominant connection. (Other join parameters are also explicit connections) where and inner joins produce a connection relationship that has no essential difference and the result is the same. But! With the standardization and development of the database language, the recessive connection has been eliminated gradually, and the new database language basically has abandoned the recessive connection, all adopt the explicit connection.

2. No matter how you connect, you can use the join clause, but when you join the same table, be careful to define an alias, or you will create an error!

A> INNER JOIN: understood as a "valid connection", the data in both tables will show the LEFT join: as "show on", such as on A.field=b.field, displays all the data present in table A and the data in the a\\b, A, b No data is displayed as null

B> right Join: Understood as "has the display", such as on A.field=b.field, displays all the data that exists in table B and the data in the a\\b, B has, a does not have the data to display null

C> full join: Understood as "fully connected", all data in both tables is displayed, actually inner + (Left-inner) + (Right-inner)

3. Joins can be divided into three types: full outreach, leftist, and right.
A completely out-of-band contains all the records for both tables.
Leftist is based on the table on the left, supplemented on the right, opposite to the right.

4. Generally want to make database query statement performance Good point follow the principle:

When you make a table-to-table connection query, the large table is in front, and the small table
Distinguish fields in different tables by field prefixes without using table aliases
The constraints in the query condition are written before the table join condition
Try to use indexed fields as query criteria

The difference between the left join and the right join in the database