Understand inner joins, left outer joins, and right outer joins of sql statements

Recommended (free):SQL Tutorial

When you first learn the database, do you have some doubts about the connection query in DQL? Do you know when and what kind of connection query should be used in what scenario?

Don’t worry, let me introduce to you my understanding of the characteristics and application scenarios of inner joins, left outer joins, and right outer joins for your reference.

The following code demonstrations are based on the name table and country table.
name table

##5 Zhao Feiyan

id	name
1	西士
2	Yang Yuhuan
3	Diao Chan
4	Wang Zhaojun

country table

id country A_ID 1 Yue people during the Spring and Autumn Period 1 2 A native of Yongle, Puzhou in the Tang Dynasty 2 3 A native of Xinzhou, Shanxi in the late Eastern Han Dynasty 3 4 People from Zigui, Nanjun in the Western Han Dynasty 4 ##5

Warring States Period	6

Note:

1) There is a relationship between b table A_ID and a table a_id

2) The connection query uses association conditions to remove mismatches Otherwise, the data will have a Cartesian product

1) Inner join

语法： select 要查询的字段 from 表名1 inner join 表名2 on 表1.字段 = 表2.字段; inner join 可简写为 逗号， 内连接特点： 只会保留完全符合on后条件的数据 应用场景： 如果两张表有外键关系可以使用内链接，因为通过内链接每一条只能返回单条记录

select * from name n inner join country c on n.id = c.N_ID;

id ##1 西士 1 元国人 1 2 Yang Yuhuan 2 People from Yongle, Puzhou, Tang Dynasty 2 Diao Chan Wang Zhaojun 2) Left outer join

name	id	country	N_ID
				##3
3	Eastern Han Dynasty A native of Xinzhou, Shanxi in the last years	3		4
4	A native of Zigui, Nanjun during the Western Han Dynasty	4

语法： select 要查询的字段 from 表1 left outer join 表2 on 表1.字段 = 表2.字段; outer 可省略 左外连接特点： 以左表为主，会保留左表中不符合on后条件的数据 应用场景： 只有部分记录可以从表2中查到，但表1想要显示所有记录，就可以和表2通过左外连接查询。

rrree

id

select * from name n left join country c on n.id=c.N_ID;

name 1 西士 Yang Yuhuan ##3 Diao Chan 3 People from Xinzhou, Shanxi Province in the late Eastern Han Dynasty 3 4 null rrree

id	country	N_ID
1	元国人	1		2
2	People from Yongle, Puzhou, Tang Dynasty	2
			##4	Wang Zhaojun
南君 Zigui people during the Western Han Dynasty	4		5	Zhao Feiyan
null	null			3) Right outer join

id

name

语法： select * from 表1 right outer join 表2 on 表1.字段 = 表2.字段; outer 可省略 右外连接特点： 以右表为主，会保留右表中不符合on后条件的数据 应用场景： 和左外连接相反 只有部分记录可以从表1中查询到，但表2想要显示所有记录， 就可以和表1通过右外连接查询。

select * from name n right join country c on n.id=c.N_ID;

id ##1 西士 1 元国人 1 People from Yongle, Puzhou, Tang Dynasty ##3 Diao Chan 3 People from Xinzhou, Shanxi in the late Eastern Han Dynasty 3 4 ##null null 5 Warring States Period 6 4) Full link id name

country	N_ID
		##2	Yang Yuhuan	2
2
	##4	Wang Zhaojun	4	A native of Zigui, Nanjun during the Western Han Dynasty

idcountry

N_ID 1 ##2 Yang Yuhuan 2 People from Yongle, Puzhou, Tang Dynasty 2 ##3 4 5 null If there is anything wrong, please point it out~ sql column~

	##1	xishi	1	元国人
Diao Chan	3	Late Eastern Han Dynasty Shanxi Xinzhou people	3
王赵君	4	Western Han Dynasty A native of Zigui, Nanjun	4
Zhao Feiyan	null	null	null
null	5	Warring States Period	6
Note: This syntax is not applicable in MySql	Okay, I have finished the introduction. I wonder if it helps you?			For more related knowledge, please pay attention to the

The above is the detailed content of Understand inner joins, left outer joins, and right outer joins of sql statements. For more information, please follow other related articles on the PHP Chinese website!