MySQL联表查询的简单示例

2023-08-23 14:12:04 137

MySql会用到联表查询，对于刚学习的新手来说，可能会理解起来有难度。下面这篇文章就来给大家详细介绍MySQL联表查询的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧

关系型数据库，免不了表之间存在各种引用与关联。这些关联是通过主键与外键搭配来形成的。所以，取数据时，很大情况下单张表无法满足需求，额外的数据则需要将其他表加入到查询中来，这便是JOIN关键字完成的操作。

MySQL中JOIN,CROSSJOIN和INNERJOIN三者语法功能上相同，可互换，而SQL标准中，INNERJOIN需要搭配ON语句。

多表联合查询时，可省略JOIN关键字，以逗号分隔多张表，此时默认会当作INNERJOIN来处理。比如,

SELECTtable1.*,
table2.*
FROMtable1,
table2;

等效于：

SELECTtable1.*,
table2.*
FROMtable1
INNERJOINtable2;

但这种通过逗号隐式指定的联表形式其优先级要低于直接通过关键字（INNERJOIN,CROSSJOIN,LEFTJOIN）指定的形式。所以t1,t2JOINt3会被解析成(t1,(t2JOINt3))而不是((t1,t2)JOINt3)

需要注意的是，当逗号形式与其他联表关键词结合时，在指定了联表条件，比如通过ON条件时，会报错。

ON指定的联表条件其语法同WHERE，所有后者可接受的表达式都可用于ON。两者看起来功能上雷同，ON一般用于指定联表条件，即表之间怎么被联合，而WHERE则用于过滤结果。
LEFTJOIN时，右边表中不满足ON或USING指定的条件时，会在结果中以NULL呈现。

SELECTleft_tbl.*
FROMleft_tblLEFTJOINright_tblONleft_tbl.id=right_tbl.id
WHEREright_tbl.idISNULL;

通过此方法可方便地过滤出右边表中不符合条件的记录。

联表查询时可为每张参与进来的表指定别名，方便在其他表达式中引用。两种方式，一个是通过AS关键字tbl_nameASalias_name，另一种是直接在表名后面跟上别名，tbl_namealias_name。

SELECTt1.name,t2.salary
FROMemployeeASt1INNERJOINinfoASt2ONt1.name=t2.name;

SELECTt1.name,t2.salary
FROMemployeet1INNERJOINinfot2ONt1.name=t2.name;

一条查询语句中的子查询必需取一个别名，这样才能在其他表达式中引用。

SELECT*FROM(SELECT1,2,3)ASt1;

USING(join_column_list)语句指定两个表中均包含的列，查询时只针对这里指定的列进行比较。

aLEFTJOINbUSING(c1,c2,c3)

NATURAL[LEFT]JOIN与INNERJOIN和LEFTJOIN配合使用了USING指定表中所有列的情况等效。
RIGHTJOIN与LEFTJOIN类似，只是最终结果是依据右边表，将左边表中不符合的在结果中以NULL呈现。为了方便在不同数据库间迁移，推荐始终使用LEFTJOIN。

一些JOIN示例：

SELECT*FROMtable1,table2;

SELECT*FROMtable1INNERJOINtable2ONtable1.id=table2.id;

SELECT*FROMtable1LEFTJOINtable2ONtable1.id=table2.id;

SELECT*FROMtable1LEFTJOINtable2USING(id);

SELECT*FROMtable1LEFTJOINtable2ONtable1.id=table2.id
LEFTJOINtable3ONtable2.id=table3.id;

NATURALJOIN的结果中不会有重复的列。因为其与USING雷同，所以USING时也没有复杂的列。

考察下面的示例：

CREATETABLEt1(iINT,jINT);
CREATETABLEt2(kINT,jINT);
INSERTINTOt1VALUES(1,1);
INSERTINTOt2VALUES(1,1);
SELECT*FROMt1NATURALJOINt2;
SELECT*FROMt1JOINt2USING(j);

查询结果：

+------+------+------+
|j |i |k |
+------+------+------+
| 1| 1| 1|
+------+------+------+
+------+------+------+
|j |i |k |
+------+------+------+
| 1| 1| 1|
+------+------+------+

结果中同名的列只出现一次，且都是值相同的那些记录。

通过向两表中插入一条新记录，令它们的j不相同，再进行测试。

mysql>INSERTINTOt1VALUES(2,2);
QueryOK,1rowaffected(0.00sec)

mysql>INSERTINTOt2VALUES(2,3);
QueryOK,1rowaffected(0.00sec)

mysql>select*fromt1naturaljoint2;
+------+------+------+
|j|i|k|
+------+------+------+
|2|2|1|
+------+------+------+
1rowinset(0.00sec)

USING和ON作为条件时其他限制的联合条件是一样的，可互相转换。但在SELECT*返回结果时，还是有差异的。前者只在USING中指定的列中返回合并后的结果，后者则针对的是表中所有列。

aLEFTJOINbUSING(c1,c2,c3)
aLEFTJOINbONa.c1=b.c1ANDa.c2=b.c2ANDa.c3=b.c3

USING情况下的返回：

COALESCE(a.c1,b.c1),COALESCE(a.c2,b.c2),COALESCE(a.c3,b.c3)

ON的返回：

a.c1,a.c2,a.c3,b.c1,b.c2,b.c3

ON语句中只能引用其操作表（operands）中的表。

CREATETABLEt1(i1INT);
CREATETABLEt2(i2INT);
CREATETABLEt3(i3INT);

针对上面的表，以下查询会报错：

mysql>SELECT*FROMt1JOINt2ON(i1=i3)JOINt3;
ERROR1054(42S22):Unknowncolumn'i3'in'onclause'

而以下查询则可以：

mysql>SELECT*FROMt1JOINt2JOINt3ON(i1=i3);
Emptyset(0.00sec)

因为此时t3在ON语句的操作范围内了。

MySQL联表查询的简单示例

热门推荐

随机推荐