1.UNION --通过组合其他两个结果表并消去表中任何重复行
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;
2.UNION ALL --通过组合其他两个结果表但不消去表中任何重复行
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
3.inner join 返回两个表中都匹配的行(可以理解为交集)
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
FROM Persons
right JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
6.full join 也称为 FULL OUTER JOIN 返回两个表中都存在的行(可以理解为并集)
SELECT column_name(s)
FROM table_name1
full join table_name2
ON table_name1.column_name=table_name2.column_name
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;
2.UNION ALL --通过组合其他两个结果表但不消去表中任何重复行
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
3.inner join 返回两个表中都匹配的行(可以理解为交集)
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
4.left join 返回左表所有的行,包括在右表中没有匹配的行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
5.right join 返回右表所有的行,包括在左表中没有匹配的行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons
right JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
6.full join 也称为 FULL OUTER JOIN 返回两个表中都存在的行(可以理解为并集)
SELECT column_name(s)
FROM table_name1
full join table_name2
ON table_name1.column_name=table_name2.column_name