SQL连接查询

一、一般连接

-- 查询商品类型id大于2的商品
SELECT * 
FROM store_goods,store_goods_type 
WHERE store_goods.goods_type = store_goods_type.goods_type_name 
AND  store_goods_type.goods_type_id > 2

 

二、SQL连接方式

1.JOIN...ON...

SELECT * 
FROM store_goods
JOIN store_goods_type
ON store_goods.goods_type = store_goods_type.goods_type_name  
WHERE store_goods_type.goods_type_id > 2

join...on...默认为内连接

 

2.LEFT JOIN...ON...

左外连接的结果集中除了包括条件的行外，还包括左表所有的行

SELECT * 
FROM store_goods
LEFT JOIN store_goods_type
ON store_goods.goods_type = store_goods_type.goods_type_name  
AND store_goods_type.goods_type_id > 2

查询结果：

 

3.RIGHT JOIN...ON...

右外连接的结果集中除了包括条件的行外，还包括右表所有的行

SELECT * 
FROM store_goods
RIGHT JOIN store_goods_type
ON store_goods.goods_type = store_goods_type.goods_type_name  
AND store_goods_type.goods_type_id > 2

查询结果：

 

 

 3.自连接

SELECT b.* 
FROM store_goods a,store_goods b
WHERE a.goods_name = '口罩'
AND b.goods_price < a.goods_price

 

SELECT b.* 
FROM store_goods a
JOIN store_goods b
ON a.goods_name = '口罩'
AND b.goods_price < a.goods_price

 

SELECT b.* 
FROM store_goods b
WHERE b.goods_price < (SELECT a.goods_price FROM store_goods a WHERE a.goods_name = '口罩')