Mysql中的join操作

join的类型

1．内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

2．外联结：分为外左联结和外右联结。

案例背景

create table java (name varchar(255));insert into java values ('java1'),('java2'),('blue'); create table mysql (name varchar(255));insert into mysql values ('mysql1'),('mysql2'),('blue');

select * from java,mysql where java.name=mysql.name;SELECT * FROM java JOIN mysql ON java.name=mysql.name;SELECT * FROM java INNER JOIN mysql ON java.name=mysql.name;SELECT * FROM java CROSS JOIN mysql ON java.name=mysql.name;SELECT * FROM java STRAIGHT_JOIN mysql ON java.name=mysql.name;

+------+------+| name | name |+------+------+| blue | blue |+------+------+

join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON condition

左联结

SELECT * FROM java LEFT JOIN mysql ON java.name=mysql.name;

+-------+------+| name | name |+-------+------+| java1 | NULL || java2 | NULL || blue | blue |+-------+------+

如果对于在LEFT JOIN中的ON或USING部分中的右表没有匹配的记录，则所有列被设置为NULL的一个行被用于右表。如果一个表在其它表中没有对应部分，您可以使用这种方法在这种表中查找记录：

SELECT * FROM java LEFT JOIN mysql ON java.name=mysql.name WHERE mysql.name IS NULL;

右联结

SELECT * FROM java RIGHT JOIN mysql ON java.name=mysql.name;

+------+--------+| name | name |+------+--------+| NULL | mysql1 || NULL | mysql2 || blue | blue |+------+--------+

外联结的语法

join_table:| table_reference LEFT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor | table_reference RIGHT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

用于为一系列的列进行命名，这些列必须同时在两个表中存在

SELECT java.*,mysql.* FROM java LEFT JOIN mysql USING (name);

+-------+------+| name | name |+-------+------+| java1 | NULL || java2 | NULL || blue | blue |+-------+------+

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c);--相当于SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;--实际上这么执行SELECT t1.id,t2.id,t3.id FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) ) WHERE t1.id=t2.id;--应该这么写SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;

更多相关文章

随机推荐