What is the difference between database left join and right join?

The difference between left join and right join in the database can be summarized in one sentence: left join where only affects the right table, right join where only affects the left table

[Recommended course: MySQL tutorial]

The difference between left join and right join in the database

Left Join

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

The search result after the left join is to display all the data in tbl1 and the data in tbl2 that meet the where condition.
In short, Left Join affects the table on the right

Right Join

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

The retrieval result is all the data of tbl2 And the data in tbl1 that meets the where condition.
In short, Right Join affects the table on the left.

Inner join (inner join)

select * FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID

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

Other content:

1. The connection statement used in the WHERE clause is called an implicit connection in the database language. INNER JOIN... The join produced by the ON clause is called an explicit join. (Other JOIN parameters are also explicit connections) There is no essential difference in the connection relationship between WHERE and INNER JOIN, and the results are the same. However, with the standardization and development of database languages, implicit connections have been gradually eliminated. Newer database languages ​​have basically abandoned implicit connections and all adopted explicit connections.

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

(1) inner join: understood as " "Effective connection", only the data in both tables will be displayed. Left join: understood as "with left display", for example, on a.field=b.field, all data existing in table a and all data in a\\b will be displayed. For some data, data that is present in A and not in B is displayed as null

(2) right join: understood as "displayed on the right", for example, on a.field=b.field, then the data in table B is displayed All the existing data and the data in a\\b, the data in B and not in A are displayed as null

(3) full join: understood as "full join", all data in the two tables are Display, it is actually inner (left-inner) (right-inner)

3. Join can be divided into primary and secondary tables. There are three types of outer joins: complete outer join, left join, right join. A complete external join contains all the records of the two tables. The left join is based on the left table and the right one is supplementary. The opposite is true for the right join.

4. Generally, to make the performance of database query statements better, follow the following principles ：

When doing connection queries between tables, the large table comes first and the small table comes first

Do not use table aliases and use field prefixes to distinguish fields in different tables

Restrictions in the query conditions should be written before the table connection conditions

Try to use indexed fields as query conditions

The above is the detailed content of What is the difference between database left join and right join?. For more information, please follow other related articles on the PHP Chinese website!