关于 LEFT JOIN 和JOIN 的理解

案例

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P

场景一

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P  and  Order.OrderNo=1

结果:  表一对一情况下查询出数据量为表 Persons

Order.OrderNo=1的左右表留下，

Order.OrderNo=！1左边保留,右表置空

场景二

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P  

and Persons.FirstName

结果: 利用主表字段在ON之后用主表字段做过滤无法过滤空的数据

场景三

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P  

WHERE  Order.OrderNo=1

结果:  表连接后合并为一张表之后进行过滤,合并后只有

Order.OrderNo=1的数据

场景四

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
JOIN Orders
ON Persons.Id_P=Orders.Id_P  

AND  Order.OrderNo=1

结果:  和场景三结果一样