Sql:left join, right join, INNER join difference, on and where condition difference

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

For example, the following two tables:

A (aid int, Aname char (10))

B (aid int, Aname char (10))

Corresponding data:

　　　　　　　　　

EXECUTE statement: SELECT * from a left JOIN B on aid=bid

　　　　

My understanding is that a left connection B, first take a record in a, according to the conditions in the on to match B, if you can match the connection as a new record, such as the 3rd row; If it does not match, the record of a and the field value are null for the B record connection, such as 1 and 2 rows.

The right connection is similar to: SELECT * from a R JOIN B on Aid=bid

　　　　

Visible, the condition of on in the record returned in the previous two connections is not necessarily true, because the field of the join condition may be a null value

and inner Join:select * from a INNER join B on aid =bid

　　　　

Guarantee the setting of the on condition.

So when used with the left Join/right join on and where, there is a difference:
For example SELECT * from a LEFT join B on aid= bid and aid>3, return

　　　　

If a select * from a LEFT join B on aid= bid where aid>3

　　　　

Because a temporary table is generated when executing such a statement, the condition in the on in this temporary table is not necessarily true, because it is possible that the field value is null.

When a temporary table is generated, it is filtered according to the Where condition, so the condition of where in the returned result must be true.

　　　　

　　

　　　　

Sql:left join, right join, INNER join difference, on and where condition difference