Difference between left JOIN, right, full back on, and where

　　Today at work encountered a problem, a table B table left join after on the condition of a table filter statement did not play I want to filter, or to the left connection is not enough to understand.

SELECT *  from student; SELECT *  from class;
The following are the results of the query for two tables:

Student Table Class table

　　　　　　　　　　　　　　　　

Let's take a look at the results returned by the Student table and class table based on the cla_id left connection

SELECT *  fromStudent Stu Left JOINclass CLA onstu.cla_id=cla.cla_id
The returned results are just as I expected, so take a look and add a stu_id=results returned after 1 conditionsSELECT *  fromStudent Stu Left JOINclass CLA onstu.cla_id=cla.cla_id andstu.stu_id=1
I couldn't figure out how the sky would return four records, and I didn't add a stu_id in the back.=1 of the conditions? Isn't filtering the student table should only return the first data? At the time, some people said no matter what the conditions were on the back,
Left table data will be fully identified, so to filter the need to put the conditions in the where, such as the SQL to change the following:SELECT *  fromStudent Stu Left JOINclass CLA onstu.cla_id=cla.cla_idWHEREstu.stu_id=1the difference between on and where is that the on condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true or not, and where condition is the condition that the temporary table is filtered after the staging table has been generated. And besides stu_id .=1 of
That record, a record in the class table that does not satisfy the filter (even if it is associated with it) is NULL, so the statement on the following is best to write only two tables associated with the statement, and cannot do one-sided filtering. can also be so simple to understand that the coordinates
First, all the records of the left table are queried, then the right table is associated, and the data of the eligible records are populated into the results of the query.  Right JoinAnd Full Joinhave the same characteristics, but the inner join is different, it can be on the
Filter processing, which means that the function is the same after on and behind. By the way, when we rename a field, it will be reported as an unrecognized column immediately after the where statement is used as a filtering condition.　　Here I take the dialect of MySQL as an example. SELECTID, BeginTime, From_unixtime ((Unix_timestamp (BeginTime)* ++Auctiontime)/ +) asEndtime fromAuctioncommenditywhereEndtime>Now (); This statement will prompt that Endtime is not a recognized column, it is feasible to filter the query as a result set in the perimeter where condition, for example:SelectA.*  from(SELECTID, BeginTime, From_unixtime ((Unix_timestamp (BeginTime)* ++Auctiontime)/ +) asEndtime fromauctioncommendity) AwhereEndtime>Now (); But in some cases it is possible, such as order byand group after having to do a filter does not require a subquery to use the processed rename field directly. About the reason .... Go to sleep first, then find out later to write blog can really take time ah ...

Difference between left JOIN, right, full back on, and where