The difference between on and where during connection query.

The difference between on and where during connection query.

The northwind data test is used.

1. Internal Connection. Inner is omitted during connection. Query the order of customer Vinet.

Select * from orders a join MERs B
On a. customerid = B. customerid and A. customerid = 'vinet'

The returned records are filtered by Vinet.

 

Select * from orders a join MERs B
On a. customerid = B. customerid
Where a. customerid = 'vinet'

Returns the same result as the preceding query.

That is to say, there is no difference between on and where in internal connection. Will filter the returned records.

2. External Connection, left connection. The same query condition.

Select * from orders a left join MERs B
On a. customerid = B. customerid and A. customerid = 'vinet'

If all the records in the left table are returned, they are not filtered by A. mermerid = 'vinet.

Select * from orders a left join MERs B
On a. customerid = B. customerid
Where a. customerid = 'vinet'

The returned records are filtered.

This is because the connection query generates a temporary table and then returns the temporary table to the user. The on condition is the condition statement used when a temporary table is generated. Records in the left table are returned no matter whether it is true or not.

The where condition is used after a temporary table is generated. At this time, it has nothing to do with the connection. It returns records that meet the conditions.