MySQL 的 JOIN 操作
JOIN 的含义就如同它的意思一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接等。
交叉连接: CROSS JOIN
如果不带 WHERE 条件子句,它将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积,比如 A 表有 n 条记录,B 表有 m 条记录,笛卡尔积产生的结果就会产生 n*m 条记录,下列 A、B、C 执行结果相同,但是效率不一样:
//A:
SELECT * FROM table1 CROSS JOIN table2;
//B:
SELECT * FROM table1,table2;
//C:
SELECT * FROM table1 a INNER JOIN table2 b;
//A:
SELECT a.*,b.* FROM table1 a,table2 b WHERE a.id=b.id
//B:
//注意:CROSS JOIN 后加条件只能用 WHERE, 不能用 ON
SELECT * FROM table1 a CROSS JOIN table2 b WHERE a.id=b.id
//C:
SELECT * FROM table1 a INNER JOIN table2 b ON a.id=b.id
一般不建议使用方法A和B,因为如果有WHERE子句的话,往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。 因此,如果两个需要求交际的表太大,将会非常非常慢,不建议使用。
内连接: INNER JOIN
两边表同时符合条件的组合,从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,有 INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN, JOIN(省略INNER) 四种写法 如果仅仅使用下列操作,内连接如果没有指定连接条件的话,和笛卡尔积的交叉连接结果一样,但是不同于笛卡尔积的地方是,没有笛卡尔积那么复杂要先生成行数乘积的数据表,内连接的效率要高于笛卡尔积的交叉连接。
SELECT * FROM table1 INNER JOIN table2
但是通常情况下,使用 INNER JOIN 需要指定连接条件。
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;
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;
SELECT * FROM table1 AS a INNER JOIN table2 AS b ON a.column=b.column
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 代替下。
自然连接: NATURE JOIN
自然连接会去除重复的列,数据库的连接运算都是自然连接,因为不允许有重复的行(元组)存在,其实就是 USING 子句的简化版,它找出两个表中相同的列作为连接条件进行连接。
// 自然连接:
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);
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);
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);
外连接: 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;
左连接: LEFT JOIN
左连接 LEFT JOIN 的含义就是求两个表的交集外加左表剩下的数据。从笛卡尔积的角度讲,就是先从笛卡尔积中挑出 ON 子句条件成立的记录,然后加上左表中剩余的记录
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
右连接: RIGHT JOIN
右连接 RIGHT JOIN 就是求两个表的交集外加右表剩下的数据。再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出 ON 子句条件成立的记录,然后加上右表中剩余的记录
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;