如何实现inner join, left/right/cross join？

Access

SQL基础知识

SQL JOIN - 用于根据两个表或多个表中的列之间的关系，从这些表中查询数据。SQL 中JOIN和inner join实际上是一样的，得到的结果也是相同的。比如，我们想得出每个人订购产品的信息。用SQL JOIN的方式为：SELECT Persons.Id_p ,Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.Id_p = Orders.Id_P;两个表之间用逗号进行连接，并且在WHERE中指定连接条件。通过观察Orders表中的数据发现Id_p = 2的那个人没有订购任何产品，所以如图所示的查询结果中也不会出现id_P = 2的记录。

SQL  inner join跟SQL join的效果是一样的。SELECT Persons.Id_p,persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons INNER JOIN Orders ON Persons.Id_p = Orders.Id_pORDER BY Persons.LastName;两个表之间用关键字 inner join进行连接，用ON指定两个表的连接条件。输出结果如图所示，通过观察发现，用inner join跟用 join的效果是一样的。

SQL Left Join与 left outer join实际上是同一个概念。使用left join 即使右表中没有匹配，也从左表返回所有的行。SELECT Persons.id_p, Persons.LastName,              Persons.FirstName, Orders.OrderNoFROM Persons LEFT JOIN Orders ON Persons.Id_p = Orders.Id_pORDER BY Persons.LastName;输出结果如图所示，通过观察发现: 虽然左表Persons.Id_p = 2的那个人在orders表（右表）中没有记录，但是在结果中还是显示了，只不过orderno列的值为空。

SQL Right Join与 right outer join实际上是同一个概念。即使左表中没有匹配，也从右表返回所有的行.SELECT Persons.id_p, Persons.LastName, Persons.FirstName,               orders.id_o, Orders.OrderNoFROM Persons RIGHT JOIN Orders ON Persons.Id_p = Orders.Id_pORDER BY Persons.LastName;输出结果如图所示，虽然右表Orders中的id_o = 5的那条记录无法正确对应persons表中的记录，但是，该行在结果中还是显示出来了。

full join, crosss join显示的结果实际上是笛卡尔积。Access中对 full join, cross join没有对应的关键字进行支持。要实现显示笛卡儿积可以用 JOIN但是不指定连接条件的方式。Micorsoft官方文档对cross join的解释如下：Cross joins are different from inner and outer joins in that they are not explicitly represented in Access. In a cross join, each row from one table is combined with each row from another table, resulting in what is called a cross product or a Cartesian product. Any time you run a query that has tables that are not explicitly joined, a cross product is the result. Cross joins are usually unintentional, but there are cases where they can be useful.所以在Access中实现cross join的SQL语句为：SELECT *FROM Persons, Ordersorder by persons.id_p;