mysql join和where

表 ：Persons

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

表：Orders

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P 等同于

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P等同于

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

left join左查询，查询左表中所有，右表中符合条件的

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P = Orders.Id_P and Persons.City='Beijing';

此句中Persons.City='Beijing'不起作用，正确做法为：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.Id_P = Orders.Id_P where Persons.City='Beijing';

但是后面跟的如果是右表条件是起作用的

版权声明：本文为博主原创文章，未经博主允许不得转载。

转载于:https://my.oschina.net/u/2324701/blog/480392