Difference between left join and right join in the database

Left Join/Right Join/inner join

A summary of left and right connections:

The left join where only affects the right table, and the right join where only affects the left table.

Left Join

Select * from tbl1 Left Join tbl2 where tbl1.ID = tbl2.ID

The search result after the left join shows all the data of tbl1 and the data that meets the where condition in tbl2.

In short, Left Join affects the right table.

Right Join

Select * from tbl1 Right Join tbl2 where tbl1.ID = tbl2.ID

The result is all the data of tbl2 and the data that meets the where condition in tbl1.

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 select * from tbl1, tbl2 where tbl1.id = tbl2.id.

Other Related Materials

1. Connection statements used in the WHERE clause are called implicit connections in the database language. Inner join ...... The connection generated by the ON clause is called an explicit connection. (Other JOIN parameters are also explicit connections.) There is no essential difference between the connection relationship between WHERE and inner join, and the result is the same. But! Recessive connections have been gradually eliminated with the development and standardization of the database language, and the new database language has basically abandoned the implicit connections, all of which adopt explicit connections.

2. join clauses can be used no matter how they are connected. When connecting to the same table, be sure to define aliases. Otherwise, an error is returned!

A> inner join: It is understood as "valid join". Only the data in both tables shows left join: It is understood as "with left display", for example, on. field = B. field to display all the data in Table a and all the data in Table a \ B. data in Table A and in Table B is displayed as null.

B> right join: it is interpreted as "right display", for example, on. field = B. field, all data in Table B and data in a \ B are displayed. data in Table B and in Table A is displayed as null.

C> full join: it is interpreted as "full join". All data in the two tables is displayed, which is actually inner + (left-inner) + (right-inner)

3. join can be divided into three types: Full outer join, left join, and right join.
Full outer join contains all records of the two tables.
The left join is dominated by the table on the left, the secondary table on the right, and the opposite table on the right.

4. The following principles should be followed to Improve the Performance of database query statements:

When performing table-to-table connection queries, the large table is in front of the small table
Table aliases are not used. Fields in different tables are distinguished by field prefixes.
The restrictions in the query conditions should be written before the table connection conditions.
Use indexed fields as the query condition whenever possible