mysql的left join、right join、inner join

A gas card table:

id, userName, cardNo

1                                                                                                                                                                                                aaa 222

BRefueling record table:

id, number, userName , cardNo,

1 1234 111

##2 234 bbb left join:

select * from B   b    left join  A a on a.userName = b.userName   where b.userName=aaa

Due to the condition after on in the above SQL, userName corresponds to multiple entries in table A instead of one, and the result set is a Cartesian product. One item in table B satisfies the remaining two items in table A. The result is 2.

select * from B   b    left join  A a on a.userName = b.userName  and a.cardNo = b.cardNo  where b.userName=aaa

Since in the above SQL, the two conditions after on can only find one unique piece of data in table A, so the result is how many pieces of data in table B satisfy where, and the result set will return how many pieces of data. Here is a piece of data returned

right join:

The following sql has the same effect as the above left join:

select * from A   a    right join  B b on a.userName = b.userName  and a.cardNo = b.cardNo  where b.userName=aaa

inner join:

select * from A   a    inner  join  B b on a.userName = b.userName  and a.cardNo = b.cardNo  where a.userName=aaa

Let’s first look at the conditions after on. If a piece of data in table A corresponds to the two conditions of on and there is only one piece of data in B, then 2 pieces of data that satisfy the where condition are returned.

select * from B   b    inner  join  A a on a.userName = b.userName  and a.cardNo = b.cardNo  where a.userName=aaa

To summarize the above: Look at the condition behind on to see whether there is one or more pieces of data in the related table.

For more related questions, please visit the PHP Chinese website:

mysql video tutorial

The above is the detailed content of mysql的left join、right join、inner join. For more information, please follow other related articles on the PHP Chinese website!