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

##5Zhao Feiyan

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

country table

idcountryA_ID1Yue people during the Spring and Autumn Period12 A native of Yongle, Puzhou in the Tang Dynasty23A native of Xinzhou, Shanxi in the late Eastern Han Dynasty34People from Zigui, Nanjun in the Western Han Dynasty4##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元国人12Yang Yuhuan2People from Yongle, Puzhou, Tang Dynasty2Diao ChanWang Zhaojun2) 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 name1西士Yang Yuhuan##3Diao Chan3 People from Xinzhou, Shanxi Province in the late Eastern Han Dynasty34null

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

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

nameid##1西士1元国人1People from Yongle, Puzhou, Tang Dynasty##3Diao Chan3People from Xinzhou, Shanxi in the late Eastern Han Dynasty34##nullnull5Warring States Period64) Full link

语法：	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;

idname

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

idcountry

N_ID1##2Yang Yuhuan2People from Yongle, Puzhou, Tang Dynasty2##345null 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!