mysql基础join链接

不知道大家平常工作对join这个关键字用的多吗？反正我是用的不多，但是突然想到这个join还真不是特别清楚这个关键字的具体用法，所以今天专门到官网上看了下join这个语法解释。

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference:
    table_reference
  | { OJ table_reference }

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [[AS] alias] [index_hint_list]
  | table_subquery [AS] alias
  | ( table_references )

join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_condition:
    ON conditional_expr
  | USING (column_list)

我们看其中关键的部分。

创建我们的两个表ta(id,name)、tb(id,age)表。

create table ta(id int,name varchar(50));

create table tb(id int,age int);

-- 插入数据
insert into ta value(1,'张三'),(2,'李四'),(4,'小明');
insert into tb value(1,20),(2,30),(3,25);

 全连接：官方给的 [inner | cross] join

1.什么都不写

2.只写join

3.inner join

4.cross join

 

 

 发现了吗，上面四种结果是一样的，这就是所谓的全连接。

左链接：官方给的left [outer] join ，中括号里面的可写可不写

select * from ta left outer join tb on ta.id = tb.id;
or
select * from ta left join tb on ta.id = tb.id;

 右链接：right [outer] join

select * from ta right outer join tb on ta.id = tb.id;
or
select * from ta right join tb on ta.id = tb.id;

我们结合左链接、右链接一起来看区别：

左链接我们发现结果中left左边表（ta表）中多余的一行数据照样显示，tb表补NULL，而右链接right右边表（tb表）中多余的一行照样显示， ta表补NULL，这就是左、右链接的区别。

总结一下就是：你要想保留左边表的所有数据则用left join，如果想保留右边表的所有数据则用right join。

但是我们会发现不管是left join 或是 right join中id列其实有一列是多余的，我们如何去除其中多余的？

官网上有段解释是这样的：

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard:

Redundant columns of a NATURAL join do not appear. Consider this set of statements:

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);
In the first SELECT statement, column j appears in both tables and thus becomes a join column, so, according to standard SQL, it should appear only once in the output, not twice. Similarly, in the second SELECT statement, column j is named in the USING clause and should appear only once in the output, not twice.

Thus, the statements produce this output:

+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
Redundant column elimination and column ordering occurs according to standard SQL, producing this display order:

First, coalesced common columns of the two joined tables, in the order in which they occur in the first table

Second, columns unique to the first table, in order in which they occur in that table

Third, columns unique to the second table, in order in which they occur in that table

我们看其中的（大家可以用词典翻译一下--大概意思就是我们可以通过nattural 或 using来过滤掉多余的列）：

In the first SELECT statement, column j appears in both tables and thus becomes a join column, so, according to standard SQL, it should appear only once in the output, not twice. Similarly, in the second SELECT statement, column j is named in the USING clause and should appear only once in the output, not twice.

我们来看下natural效果：

 如果还想保留其中一个表的所有数据：我们可以使用natural (left | right) join：

我们用using来看下：

除了这些还有一个不知道大家注意到没，ta表中多余的那行，或是tb表中多余的那行数据该怎么拿？

 我们看官网上怎么给我们的：

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:


SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

是不是so easy啊！我们只需要在我们的sql最后加上where table_name.col_name IS NULL就可以了

select * from ta left join tb using(id) where tb.id is null;