表 :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';
但是后面跟的如果是右表条件是起作用的