MySQL: Left Join, Right Join and Inner Join, Outer Join
Example to explain differences of left join and right join :
#################
select * from goods;
+----------+--------+------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------+
| 1 | 1 | CDMA Phone |
| 2 | 1 | GSM Phone |
| 3 | 1 | 3G Phone |
| 4 | 3 | TP Phone |
+----------+--------+------------+
select * from cat;
+--------+---------------+
| cat_id | cat_name |
+--------+---------------+
| 1 | Mobile Phone |
| 2 | Settled Phone |
+--------+---------------+
#################
#Left join: Use left table as criterion.
#Pay attention to the last row.
select goods.goods_id, goods.cat_id, goods.goods_name, cat.cat_name from goods left join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------------+
| goods_id | cat_id | goods_name | cat_name |
+----------+--------+------------+--------------+
| 1 | 1 | CDMA Phone | Mobile Phone |
| 2 | 1 | GSM Phone | Mobile Phone |
| 3 | 1 | 3G Phone | Mobile Phone |
| 4 | 3 | TP Phone | NULL |
+----------+--------+------------+--------------+
select goods.*, cat.* from cat left join goods on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+---------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
| NULL | NULL | NULL | 2 | Settled Phone |
+----------+--------+------------+--------+---------------+
#Right join: Use right table as criterion
select goods.*, cat.* from cat right join goods on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+--------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
| 4 | 3 | TP Phone | NULL | NULL |
+----------+--------+------------+--------+--------------+
select goods.*, cat.* from goods right join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+---------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
| NULL | NULL | NULL | 2 | Settled Phone |
+----------+--------+------------+--------+---------------+
#Comment:
#table A left join table B == table B right join table A
#Both use table A as criterion
#Use the table that actually stands at the LEFT side as criterion
Example to explain Inner Join and Outter Join:
#Use the previous table
#Left join
select goods.goods_id, goods.cat_id, goods.goods_name, cat.cat_name from goods left join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------------+
| goods_id | cat_id | goods_name | cat_name |
+----------+--------+------------+--------------+
| 1 | 1 | CDMA Phone | Mobile Phone |
| 2 | 1 | GSM Phone | Mobile Phone |
| 3 | 1 | 3G Phone | Mobile Phone |
| 4 | 3 | TP Phone | NULL |
+----------+--------+------------+--------------+
#Right join
select goods.*, cat.* from cat left join goods on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+---------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
| NULL | NULL | NULL | 2 | Settled Phone |
+----------+--------+------------+--------+---------------+
#Inner join
select goods.*, cat.* from goods inner join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+--------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
+----------+--------+------------+--------+--------------+
#Outter join
select goods.*, cat.* from goods left join cat on goods.cat_id = cat.cat_id union select goods.*, cat.* from goods right join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+---------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
| 4 | 3 | TP Phone | NULL | NULL |
| NULL | NULL | NULL | 2 | Settled Phone |
+----------+--------+------------+--------+---------------+
Comment:
1) Inner join will not use left table or right table as criterion.
2) Just think as table A cartesian product table B. And then apply the filter on the result set.
3) Or regard Inner Join as the Intersection of Left Join and Right Join.
4) So how to get the Union of Left Join and Right Join? ----> Outter Join is not supported by MySQL!----> But we can use UNION to combine the result set of left join and right join to realize this!
Comments:
1) How to join more than to tables?---->Will be explained in detail in next charpter.
上一篇: left join on and
下一篇: 数据类型转换
推荐阅读
-
MySQL: Left Join, Right Join and Inner Join, Outer Join
-
left join on and
-
mysql left join 多个表 博客分类: database MySQLSQLSQL ServerCC++
-
left join on 和 where 的使用 博客分类: sqlsql mysql oracle lefton
-
MySQL LEFT JOIN 语法 博客分类: MySQL leftjoinmysql
-
left join时on条件与where条件的区别 博客分类: Database 链接leftrightinnerjoin
-
Sql查询左连接(left join),右连接(right join),内连接(inner join) 内连接 博客分类: SQL sqlleftinnerjoinright
-
SQL关联查询————LEFT JOIN关键字的使用
-
MySQL - Join关联查询优化 --- NLJ及BNL 算法初探
-
Hibernate中用left join(左外连接)查询映射中没有关联关系的两个表记录问题