SQL Join子句，表连主要用在select语句中，接知解老把两个或多个表的识详行结合起来，基于这些表之间的男孩共同字段(往往是id字段)来查询，从多个表中返回满足条件的表连所有行。 
常见join子句类型 常见join子句类型有INNER JOIN(同JOIN)、接知解老LEFT JOIN、识详RIGHT JOIN、男孩FULL JOIN，表连其中第一种为内连接，接知解老后三种为外连接。识详 不同的男孩join子句类型区别如下图所示： 
实践数据准备 #1.建表语句和内容插入。a是表连左边的表，b是接知解老右边的表 create table a(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);) create table b(aid int not null,city varchar(20) not null,telnum char(11)); insert into a values(1,oldboy,35),(2,oldgirl,28),(3,inca,22),(5,zs,23); insert into b values(1,bj,135),(2,sz,189),(3,sh,166),(4,hz,187); #2.最终数据结果。云南idc服务商 mysql> select * from a; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | oldboy | 35 | | 2 | oldgirl | 28 | | 3 | inca | 22 | | 5 | zs | 23 | +----+---------+------+ 4 rows in set (0.00 sec) mysql> select * from b; +-----+------+--------+ | aid | city | telnum | +-----+------+--------+ | 1 | bj | 135 | | 2 | sz | 189 | | 3 | sh | 166 | | 4 | hz | 187 | +-----+------+--------+ 4 rows in set (0.00 sec) #提示：注意两个表的识详ID列有相同的id，也有不同的ID 内连接(inner join)实践 内连接是生产最常见的连接，只输出关联ID匹配的行。内连接有三种语句，都是等同的。 mysql> select * from a join b on a.id=b.aid; #<==省略了inner关键词。 mysql> select * from a inner join b on a.id=b.aid; #<==带inner关键词。 mysql> select * from a,b where a.id=b.aid; 以上3个语句输出的信息相同，即为两个表中id相同的行信息。 +----+---------+------+-----+------+--------+ | id | name | age | aid | city | telnum | +----+---------+------+-----+------+--------+ | 1 | oldboy | 35 | 1 | bj | 135 | | 2 | oldgirl | 28 | 2 | sz | 189 | | 3 | inca | 22 | 3 | sh | 166 | 左外连接(left join)实践 LEFT JOIN子句会返回左表全部行和右表满足ON关联条件行，如果左表行在右表中没有匹配，那么这一行右表中对应数据用NULL代替。源码下载 mysql> select * from a left join b on a.id=b.aid; #执行SQL输出结果如下。 +----+---------+------+------+------+--------+ | id | name | age | aid | city | telnum | +----+---------+------+------+------+--------+ | 1 | oldboy | 35 | 1 | bj | 135 | | 2 | oldgirl | 28 | 2 | sz | 189 | | 3 | inca | 22 | 3 | sh | 166 | | 5 | zs | 23 | NULL | NULL | NULL | +----+---------+------+------+------+--------+ 右外连接(right join)实践 RIGHT JOIN子句会右表全部行和左表满足ON关联条件行，如果右表行在左表中没有匹配，那么这一行左表中对应数据用NULL代替。 mysql> select * from a right outer join b on a.id=b.aid; #<==outer可省略。 +------+---------+------+-----+------+--------+ | id | name | age | aid | city | telnum | +------+---------+------+-----+------+--------+ | 1 | oldboy | 35 | 1 | bj | 135 | | 2 | oldgirl | 28 | 2 | sz | 189 | | 3 | inca | 22 | 3 | sh | 166 | | NULL | NULL | NULL | 4 | hz | 187 | +------+---------+------+-----+------+--------+ mysql> select * from a right join b on a.id=b.aid; +------+---------+------+-----+------+--------+ | id | name | age | aid | city | telnum | +------+---------+------+-----+------+--------+ | 1 | oldboy | 35 | 1 | bj | 135 | | 2 | oldgirl | 28 | 2 | sz | 189 | | 3 | inca | 22 | 3 | sh | 166 | | NULL | NULL | NULL | 4 | hz | 187 | +------+---------+------+-----+------+--------+ 外连接(outer join)实践 outer join有的库叫做FULL JOIN会返回左表和右表所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替，不过mysql暂不支持这种连接，但可以用union all连接左连接和右连接来实现。 mysql> (select * from a left join b on a.id=b.aid) -> union all -> (select * from a right join b on a.id=b.aid); +------+---------+------+------+------+--------+ | id | name | age | aid | city | telnum | +------+---------+------+------+------+--------+ | 1 | oldboy | 35 | 1 | bj | 135 | | 2 | oldgirl | 28 | 2 | sz | 189 | | 3 | inca | 22 | 3 | sh | 166 | | 5 | zs | 23 | NULL | NULL | NULL | | 1 | oldboy | 35 | 1 | bj | 135 | | 2 | oldgirl | 28 | 2 | sz | 189 | | 3 | inca | 22 | 3 | sh | 166 | | NULL | NULL | NULL | 4 | hz | 187 | +------+---------+------+------+------+--------+ 后记： 有关mysql的join，其实可以简单得执行? join，查看MySQL支持的连接类型及例子，一般人我都不告诉他。 joined_table: { table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification] | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor } join_specification: { ON search_condition | USING (join_column_list) }云服务器 |
喜欢
讨厌