连接INNER - PostgreSQL

我们现在操作来操作去都是同一张表。那么可不可以操作两张表。答案是可以。就需要用到表关联。表关联就是两张表按照某个字段来进行关联。在PostgreSQL中，有以下类型的连接：内连接(INNER JOIN) 左外连接(LEFT OUTER JOIN) 右外连接(RIGHT OUTER JOIN) 全连接(FULL OUTER JOIN) 跨连接(CROSS JOIN)

INNER JOIN

PostgreSQL内部连接也被称为连接或简单连接。这是最常见的连接类型。此连接返回满足连接条件的多个表中的所有行。

语法

SELECT table1.columns, table2.columns  
FROM table1  
INNER JOIN table2  
ON table1.common_filed = table2.common_field;

为了方便演示我们创建另外一张表创建一张Province表，用来表示省份。

CREATE TABLE public.province (    
    "ID" integer NOT NULL,    
    "Name" character varying(100),    
    PRIMARY KEY ("ID") 
);  
ALTER TABLE public.province    
OWNER to postgres; 
COMMENT ON TABLE public.province    
IS '省份表';
Code language: PHP (php)

然后往这个表中增加一些记录

INSERT INTO public.province( "ID", "Name")    
VALUES (1, '香港'),(2, '北京'),(3, '浙江'),(4, '广东');
Code language: PHP (php)

然后修改student表增加一个proid 表示这个学生所属的省份然后直接再界面上面修改每个学生的省份ID 修改完成点上门箭头的按钮保存否则不会保存我们这里估计没有proid=2的学生这样目的是表连接后我们看不到北京另外有一个proid是5 这个没有对应的省份

下面我们来看两张表去内连接

SELECT student.name,province.name   
FROM student    
INNER JOIN province   
ON student.proid = province.ID;

ERROR: 错误: 字段 province.id 不存在 LINE 6: ON student.proid = province.ID; ^ SQL state: 42703 Character: 97

错误原因

我们建表的时候，省份表的主键字段名写的是带双引号的 "ID"，大写ID。 PostgreSQL默认会把SQL里的标识符自动转小写，所以写 province.ID 会被数据库识别成 province.id（小写id）。但是表里面的字段名称是大写 "ID"，大小写不一样，数据库找不到这个字段，就报错。

两种解决办法：方案1：ON后面字段名加上双引号，保留大写

SELECT student.name,province."Name"   
FROM student    
INNER JOIN province   
ON student.proid = province."ID";
Code language: JavaScript (javascript)

方案2（推荐）：建表的时候不要给字段名加双引号，字段名直接小写 id，name。PostgreSQL默认小写，不容易踩坑。

笔记要点：内连接INNER JOIN：只返回两边表匹配成功的记录。 student里面proid=5，省份表没有ID=5的记录，这条学生记录不会出现在结果。省份表ID=2北京，没有学生proid=2，北京这条省份记录也不会出现在结果。

了解连接-内连接INNER JOIN

ERROR: 错误: 字段 province.id 不存在 LINE 3: on student.pid = province.id ^ SQL 状态: 42703 字符: 60

我们要给后面的province.id 的这个字段加个双引号这样才认为没有问题。

select * from student
inner join province
on student.pid = province."ID"
Code language: JavaScript (javascript)

select * from student inner join province on student."pid" = province."ID"
Code language: JavaScript (javascript)

如果只想显示部分字段

select student.* ,province."Name"
from student
inner join province
on student."pid" = province."ID"
Code language: JavaScript (javascript)

大家要用双引号不要用单引号错误写法：

select *
from student
inner join province
on student.'pid'= province.'ID'
Code language: JavaScript (javascript)

笔记要点：

双引号 "：用来包裹标识符（表名、字段名），保留大小写。我们建表时字段写了 "ID"、"Name"，查询时就必须用双引号。
单引号 '：只用来包裹字符串常量，不能用来包裹字段名。用单引号写字段名会直接报错。
INNER JOIN 只会返回 student.pid 和 province.”ID” 相等的记录，两边匹配不上的数据都会被过滤掉。

左外连接left outer join

外连接是内联的延伸，外连接有三种类型。它们分别如下 – 左外连接右外连接全外连接

左外连接返回从“ON”条件中指定的左侧表中的所有行，只返回满足条件的另一个表中的行。也就是左边的表全部显示，右边的表满足on中的条件才显示。

我们继续以上次的student和province为例子。先看一下两张表的数据。首先如果是inner join 那么就不显示苏和周，一般左表就是from 跟着的表，也就是先开始出现的表

SELECT *
	FROM student
	inner join province
	on student."pid" = province."ID"
Code language: JavaScript (javascript)

下面我们来看以student为左表进行表连接查询。

select * from student left outer join province on student."pid" = province."ID"
Code language: JavaScript (javascript)

注意这里的province.”ID” 是区分大小写。不要写成了province.”id” 还有是left outer join 不是left out join 注意后面的outer 不要漏了er

我们可以发现左边整个表都出现了所有记录都出现，右边则只有匹配的才显示。所以苏和周由于匹配不到，则右边省份字段显示null。并且张国荣和张学友由于pid都是3 所以左边也重复出现了浙江这都是正确的。

假设 province表有重复的id呢？比如有两个北京我们这里有唯一约束所以没法创建两个ID=2 的北京我们把province的ID的唯一约束去掉然后添加两个ID=2的如果右表真的有ID=2的两条数据。那么左边就可能会重复。

笔记要点：

left outer join 可以简写为 left join，效果一样
左表全部保留；右表匹配不上的字段填充 NULL
当右表有多条记录匹配左表同一条记录，左表记录会产生多行重复

右外连接RIGHT OUTER JOIN

右外连接和左外连接刚好相反。就是右边的表全部出现。左边的表符合on条件的才出现。其实右外连接可以通过左外连接来实现。下面我们就以上节课为例子。

下面是右外连接SQL

SELECT * 
	FROM student 
	right outer join province 
	on student."pid" = province."ID"
Code language: JavaScript (javascript)

浙江出现两次因为 student表里面有两个pid=3的学生然后北京就算左边没有和它对应的学生记录，student相关字段就会填充null

right outer join 可以简写为 right join，效果一样
右表（这里是province）所有记录全部保留；左表匹配不上的字段填充 NULL
右外连接等价写法：交换两张表位置，改成左外连接

SELECT * 
FROM province 
left outer join student 
on province."ID" = student."pid"
Code language: JavaScript (javascript)

下面是左外链接

下面是右外链接

全外连接FULL OUTER JOIN

全外连接从左表和右表中返回所有行。它将NULL置于不满足连接条件的位置。

也就是左右两边都要出现，能匹配的就对上（PID=ID）显示在一行里面，不能匹配的就另外一边为null。反正就是左右两个表的所有记录都要出现。

SELECT * 
	FROM student 
	full outer join province 
	on student."pid" = province."ID"
Code language: JavaScript (javascript)

full outer join 可以简写为 full join
左表student全部记录保留，右表province全部记录保留
两边匹配上的记录合并成一行；匹配不上的，另一侧字段填充NULL
相当于左外连接和右外连接的结果合并在一起

交叉连接CROSS JOIN

PostgreSQL跨连接(CROSS JOIN)将第一个表的每一行与第二个表的每一行相匹配。它也被称为笛卡尔积。如果table1具有“x”行，而table2具有“y”行，则所得到的表将具有(x * y)行。

语法：

SELECT columns FROM table1 CROSS JOIN table2

就是两张表每个行都和另外一张表互相配对。那么就没有on的这个关联条件了。

SELECT * 
	FROM student 
	cross join province

CROSS JOIN 不需要写 ON 条件，写了会报错
结果行数 = student总行数 × province总行数
使用要小心，表数据量大的时候，笛卡尔积结果会爆炸，产生海量数据

Previous: 条件查询

Next: 视图View