MySQL中的多表查询

对于多表查询，连接 n张表,至少需要 n-1个连接条件。
给定两张表product，productdir

1.笛卡儿积多表查询

SELECT*FROM product,productdir 

2.内连接

隐式内连接
SELECT*FROM product p,productdir d WHERE p.dir_id=d.id 
显示内连接
SELECT*FROM product p JOIN productdir d ON p.dir_id=d.id 

3.外连接

左外连接
SELECT*FROM product p LEFT JOIN productdir d ON p.dir_id=d.id

右外连接
SELECT * from product p RIGHT join productdir d on p.dir_id=d.id

4.自连接

SELECT sub.dirName,super.dirName FROM productdir super,productdir sub WHERE sub.parent_id = super.id

5.子查询

单行单列子查询

SELECT * FROM product
WHERE salePrice > (
	SELECT salePrice FROM product WHERE productName = '罗技MX1100'
)

单行多列子查询

SELECT * FROM product
WHERE (dir_id,cutoff)=(
	SELECT dir_id,cutoff FROM product WHERE productName = '罗技M100'
)