文章目录
一 连接查询介绍
mysql有以下几种连接查询,如下
- 内连接:join, inner join
- 外连接:left join, right join, left outer join, right outer join, union
- 交叉连接(笛卡尔积):cross join
二 实例说明
需要创建两张表,如下
1. join和inner join
join和inner join是等价的,都是内连接,内连接也叫等值连接。
SELECT a.*,b.*
FROM my_products AS a JOIN my_orders AS b
ON a.product_id = b.goods_id;
#或者
SELECT a.*,b.*
FROM my_products AS a INNER JOIN my_orders AS b
ON a.product_id = b.goods_id
可以看到,只有满足关键字ON后的条件的记录才被筛选出来,满足条件的记录同时也是两个表的关联部分,所有也叫内部连接或者等值连接。
2. left join和left outer join
left join和 left outer join是等价的,称为左外连接。左外连接是以join左边的表为基准查询左表所有数据,以及join右边表相关联的数据,如果右表没有,就以NULL补齐。如下
SELECT a.*,b.*
FROM my_products AS a LEFT JOIN my_orders AS b
ON a.product_id = b.goods_id;
#或者
SELECT a.*,b.*
FROM my_products AS a LEFT OUTER JOIN my_orders AS b
ON a.product_id = b.goods_id
3. right join和right outer join
left join和 left outer join是等价的,称为右外连接。右外连接是以join右边的表为基准查询右表所有数据,以及join左边表相关联的数据,如果左表没有,就以NULL补齐。如下
SELECT a.*,b.*
FROM my_products AS a RIGHT JOIN my_orders AS b
ON a.product_id = b.goods_id;
#或者
SELECT a.*,b.*
FROM my_products AS a RIGHT OUTER JOIN my_orders AS b
ON a.product_id = b.goods_id
4. union
union的作用是把两个结果集拼接在一起,默认自动去掉重复的行,如果不想去掉重复的行可在union后面加一个all关键字,即union all。如下
#不去重
SELECT product_id,product_name,product_number
FROM my_products
WHERE product_number > 20
UNION ALL
SELECT product_id,product_name,product_number
FROM my_products
WHERE product_number <= 28;
#去重
SELECT product_id,product_name,product_number
FROM my_products
WHERE product_number > 20
UNION
SELECT product_id,product_name,product_number
FROM my_products
WHERE product_number <= 28;
注意:在用union或者union进行组合查询时每个select语句返回的列名必须相同。
5. cross join
cross join是进行交叉查询,也称笛卡尔积,即A表里的每条数据都要与B表里每条数据进行组合。如下
SELECT *
FROM my_products CROSS JOIN my_orders
ORDER BY product_id