join的含义就如英文单词“join”一样，连接两张表，大致分为内连接，外连接，右连接，左连接，自然连接。
先创建两个表，下面用于示例
create table t_blog( id int primary key auto_increment, title varchar(50), typeid int ); select * from t_blog; +----+-------+--------+ | id | title | typeid | +----+-------+--------+ | 1 | aaa | 1 | | 2 | bbb | 2 | | 3 | ccc | 3 | | 4 | ddd | 4 | | 5 | eee | 4 | | 6 | fff | 3 | | 7 | ggg | 2 | | 8 | hhh | null | | 9 | iii | null | | 10 | jjj | null | +----+-------+--------+ -- 博客的类别 create table t_type( id int primary key auto_increment, name varchar(20) ); select * from t_type; +----+------------+ | id | name | +----+------------+ | 1 | c++ | | 2 | c | | 3 | java | | 4 | c# | | 5 | javascript | +----+------------+
笛卡尔积：cross join要理解各种join首先要理解笛卡尔积。笛卡尔积是将a表中的每个记录与b表中的每个记录进行组合。因此，当a表中有n条记录且b表中有m条记录时，笛卡尔积操作得到的结果将会有n*m条记录。下面的例子，t_blog有10条记录，t_type有5条记录，所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。
select * from t_blog cross join t_type; select * from t_blog inner join t_type; select * from t_blog,t_type; select * from t_blog nature join t_type; select * from t_blog natura join t_type; +----+-------+--------+----+------------+ | id | title | typeid | id | name | +----+-------+--------+----+------------+ | 1 | aaa | 1 | 1 | c++ | | 1 | aaa | 1 | 2 | c | | 1 | aaa | 1 | 3 | java | | 1 | aaa | 1 | 4 | c# | | 1 | aaa | 1 | 5 | javascript | | 2 | bbb | 2 | 1 | c++ | | 2 | bbb | 2 | 2 | c | | 2 | bbb | 2 | 3 | java | | 2 | bbb | 2 | 4 | c# | | 2 | bbb | 2 | 5 | javascript | | 3 | ccc | 3 | 1 | c++ | | 3 | ccc | 3 | 2 | c | | 3 | ccc | 3 | 3 | java | | 3 | ccc | 3 | 4 | c# | | 3 | ccc | 3 | 5 | javascript | | 4 | ddd | 4 | 1 | c++ | | 4 | ddd | 4 | 2 | c | | 4 | ddd | 4 | 3 | java | | 4 | ddd | 4 | 4 | c# | | 4 | ddd | 4 | 5 | javascript | | 5 | eee | 4 | 1 | c++ | | 5 | eee | 4 | 2 | c | | 5 | eee | 4 | 3 | java | | 5 | eee | 4 | 4 | c# | | 5 | eee | 4 | 5 | javascript | | 6 | fff | 3 | 1 | c++ | | 6 | fff | 3 | 2 | c | | 6 | fff | 3 | 3 | java | | 6 | fff | 3 | 4 | c# | | 6 | fff | 3 | 5 | javascript | | 7 | ggg | 2 | 1 | c++ | | 7 | ggg | 2 | 2 | c | | 7 | ggg | 2 | 3 | java | | 7 | ggg | 2 | 4 | c# | | 7 | ggg | 2 | 5 | javascript | | 8 | hhh | null | 1 | c++ | | 8 | hhh | null | 2 | c | | 8 | hhh | null | 3 | java | | 8 | hhh | null | 4 | c# | | 8 | hhh | null | 5 | javascript | | 9 | iii | null | 1 | c++ | | 9 | iii | null | 2 | c | | 9 | iii | null | 3 | java | | 9 | iii | null | 4 | c# | | 9 | iii | null | 5 | javascript | | 10 | jjj | null | 1 | c++ | | 10 | jjj | null | 2 | c | | 10 | jjj | null | 3 | java | | 10 | jjj | null | 4 | c# | | 10 | jjj | null | 5 | javascript | +----+-------+--------+----+------------+
内连接：inner join内连接inner join是最常用的连接操作。从数学的角度来说，这就是要计算两个表的相交部分；而从笛卡尔积的角度来看，就是要从笛卡尔积中筛选出满足on子句条件的记录。有inner join，where（等值连接），straight_join,join(省略inner)四种写法。
select * from t_blog inner join t_type on t_blog.typeid=t_type.id; select * from t_blog,t_type where t_blog.typeid=t_type.id; select * from t_blog straight_join t_type on t_blog.typeid=t_type.id; --注意stright_join有个下划线 select * from t_blog join t_type on t_blog.typeid=t_type.id; +----+-------+--------+----+------+ | id | title | typeid | id | name | +----+-------+--------+----+------+ | 1 | aaa | 1 | 1 | c++ | | 2 | bbb | 2 | 2 | c | | 7 | ggg | 2 | 2 | c | | 3 | ccc | 3 | 3 | java | | 6 | fff | 3 | 3 | java | | 4 | ddd | 4 | 4 | c# | | 5 | eee | 4 | 4 | c# | +----+-------+--------+----+------+
左连接：left join左连接left join的含义就是求两个表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出on子句条件成立的记录，然后加上左表中剩余的记录（见最后三条）。
select * from t_blog left join t_type on t_blog.typeid=t_type.id; +----+-------+--------+------+------+ | id | title | typeid | id | name | +----+-------+--------+------+------+ | 1 | aaa | 1 | 1 | c++ | | 2 | bbb | 2 | 2 | c | | 7 | ggg | 2 | 2 | c | | 3 | ccc | 3 | 3 | java | | 6 | fff | 3 | 3 | java | | 4 | ddd | 4 | 4 | c# | | 5 | eee | 4 | 4 | c# | | 8 | hhh | null | null | null | | 9 | iii | null | null | null | | 10 | jjj | null | null | null | +----+-------+--------+------+------+
右连接：right join同理右连接right join就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出on子句条件成立的记录，然后加上右表中剩余的记录（见最后一条）。
select * from t_blog right join t_type on t_blog.typeid=t_type.id; +------+-------+--------+----+------------+ | id | title | typeid | id | name | +------+-------+--------+----+------------+ | 1 | aaa | 1 | 1 | c++ | | 2 | bbb | 2 | 2 | c | | 3 | ccc | 3 | 3 | java | | 4 | ddd | 4 | 4 | c# | | 5 | eee | 4 | 4 | c# | | 6 | fff | 3 | 3 | java | | 7 | ggg | 2 | 2 | c | | null | null | null | 5 | javascript | +------+-------+--------+----+------------+
外连接：outer join外连接就是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出on子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。mysql不支持outer join，但是我们可以通过将左连接和右连接的结果进行union操作来实现。
select * from t_blog left join t_type on t_blog.typeid=t_type.id union select * from t_blog right join t_type on t_blog.typeid=t_type.id; +------+-------+--------+------+------------+ | id | title | typeid | id | name | +------+-------+--------+------+------------+ | 1 | aaa | 1 | 1 | c++ | | 2 | bbb | 2 | 2 | c | | 7 | ggg | 2 | 2 | c | | 3 | ccc | 3 | 3 | java | | 6 | fff | 3 | 3 | java | | 4 | ddd | 4 | 4 | c# | | 5 | eee | 4 | 4 | c# | | 8 | hhh | null | null | null | | 9 | iii | null | null | null | | 10 | jjj | null | null | null | | null | null | null | 5 | javascript | +------+-------+--------+------+------------+
using子句mysql中连接sql语句中，on子句的语法格式为：table1.column_name = table2.column_name。当模式设计对联接表的列采用了相同的命名样式时，就可以使用 using 语法来简化 on 语法，格式为：using(column_name)。
所以，using的功能相当于on，区别在于using指定一个属性名用于连接两个表，而on指定一个条件。另外，select *时，using会去除using指定的列，而on不会。实例如下。
select * from t_blog inner join t_type on t_blog.typeid =t_type.id; +----+-------+--------+----+------+ | id | title | typeid | id | name | +----+-------+--------+----+------+ | 1 | aaa | 1 | 1 | c++ | | 2 | bbb | 2 | 2 | c | | 7 | ggg | 2 | 2 | c | | 3 | ccc | 3 | 3 | java | | 6 | fff | 3 | 3 | java | | 4 | ddd | 4 | 4 | c# | | 5 | eee | 4 | 4 | c# | +----+-------+--------+----+------+ select * from t_blog inner join t_type using(typeid); error 1054 (42s22): unknown column 'typeid' in 'from clause' select * from t_blog inner join t_type using(id); -- 应为t_blog的typeid与t_type的id不同名，无法用using，这里用id代替下。 +----+-------+--------+------------+ | id | title | typeid | name | +----+-------+--------+------------+ | 1 | aaa | 1 | c++ | | 2 | bbb | 2 | c | | 3 | ccc | 3 | java | | 4 | ddd | 4 | c# | | 5 | eee | 4 | javascript | +----+-------+--------+------------+
自然连接：nature join自然连接就是using子句的简化版，它找出两个表中相同的列作为连接条件进行连接。有左自然连接，右自然连接和普通自然连接之分。在t_blog和t_type示例中，两个表相同的列是id，所以会拿id作为连接条件。
另外千万分清下面三条语句的区别 。
自然连接:select * from t_blog natural join t_type;
笛卡尔积:select * from t_blog natura join t_type;
笛卡尔积:select * from t_blog nature join t_type;
select * from t_blog natural join t_type; select t_blog.id,title,typeid,t_type.name from t_blog,t_type where t_blog.id=t_type.id; select t_blog.id,title,typeid,t_type.name from t_blog inner join t_type on t_blog.id=t_type.id; select t_blog.id,title,typeid,t_type.name from t_blog inner join t_type using(id); +----+-------+--------+------------+ | id | title | typeid | name | | 1 | aaa | 1 | c++ | | 2 | bbb | 2 | c | | 3 | ccc | 3 | java | | 4 | ddd | 4 | c# | | 5 | eee | 4 | javascript | select * from t_blog natural left join t_type; select t_blog.id,title,typeid,t_type.name from t_blog left join t_type on t_blog.id=t_type.id; select t_blog.id,title,typeid,t_type.name from t_blog left join t_type using(id); | 6 | fff | 3 | null | | 7 | ggg | 2 | null | | 8 | hhh | null | null | | 9 | iii | null | null | | 10 | jjj | null | null | select * from t_blog natural right join t_type; select t_blog.id,title,typeid,t_type.name from t_blog right join t_type on t_blog.id=t_type.id; select t_blog.id,title,typeid,t_type.name from t_blog right join t_type using(id); +----+------------+-------+--------+ | id | name | title | typeid | | 1 | c++ | aaa | 1 | | 2 | c | bbb | 2 | | 3 | java | ccc | 3 | | 4 | c# | ddd | 4 | | 5 | javascript | eee | 4 |
以上就是mysql中如何使用join的详细内容。