The LEFT join and right join in the database are distinguished from the other

Left Join/right Join/inner Join related

A word about the summary of the left and right connections:

Left JOIN to the right table, the right connection where only affects the left table.

Left Join

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

The retrieval result after left join is to display all the data of the TBL1 and the data that satisfies the where condition in the 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 retrieved results are all TBL2 data and the data in the TBL1 that meet the Where condition.

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

INNER JOIN

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

The function is the same as the select * from Tbl1,tbl2 where tbl1.id=tbl2.id.

other relevant information

1. The connection statement used in the WHERE clause, in the database language, is called an implicit connection. INNER JOIN ... An ON clause produces a connection called a dominant connection. (Other join parameters are also dominant connections) the connection between the WHERE and the inner join is not fundamentally different and the result is the same. But! With the specification and development of the database language, the recessive connection has been eliminated gradually, and the new database language has basically abandoned the recessive connection, all using the explicit connection.

2. No matter how the connection, you can use the join clause, but when connected to the same table, pay attention to the definition of aliases, or generate errors!

A> INNER join: As a "valid connection", the data in both tables will show a LEFT join: "There is a left-hand display", such as on A.field=b.field, which shows all the data in table A and all the data in the a\\b. b No data is displayed in NULL

B> right join: interpreted as "there is a right-hand display", such as on A.field=b.field, it shows all the data that exists in table B and all the data in a\\b, and the data in B with and a does not appear in null

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

3. Join can be divided into primary and secondary table outside the join has three kinds of types: complete outreach, leftist, right link.
Full outreach contains all the records for both tables.
Leftist is the table on the left, supplemented by the right, the opposite

4. Generally to make the database query statement performance better follow the principle:

When making a connection query between tables and tables, the large table is in front, and the small table is
Distinguish fields in different tables by field prefixes without using table aliases
Constraints in a query condition to be written before the table join condition
Try to use indexed fields as query criteria