MySQL的幾種串連 join/inner join/cross join/逗號/left join/right join/natural join

標籤：--   不同   語法錯誤   birt   表串連   style   記錄   概念   ros   

轉載請註明出處！！之前資料表串連操作多使用逗號或者join，對幾種串連的概念一直渾渾噩噩，最近研究了一波，把這些串連的區別搞明白了。 串連：A xjoin B（主表 操作 關聯表） select過程：from->where->group by->having->order by->limit在不使用on文法時，join、inner join、逗號、cross join結果相同，都是取2個表的笛卡爾積。逗號與其他動作符優先順序不同，所以有可能產生語法錯誤，盡量減少用逗號 join、inner join、cross join支援on和using文法，逗號不支援on和using文法on文法：篩選串連後的結果，兩表的對應列值相同才在結果集中，可以通過and串連多個列值的匹配要求，列名可以不同     select * from tb_test1 inner join tb_student on tb_test1.id=tb_student.id;using文法：篩選串連後的結果，兩表的對應列值相同才在結果集中，括弧內用多個列名要求用逗號串連，列名必須相同     select * from tb_test1 cross join tb_student using(id); 僅MySQL中！：（MySQL中沒有全外串連，用UNION操作實現吧）join=inner join=cross join（笛卡爾積）left join=left outer joinright join=right outer join sample資料：  select * from tb_test1;+----+-----------+--------+-------+| id | name      | gender | score |+----+-----------+--------+-------+|  1 | 李毅      | 男     | 95.30 ||  2 | 二丫      | 女     | 95.30 ||  3 | 張三      | 女     | 95.30 ||  4 | 李四      | 女     | 95.30 ||  7 | 胡魯瓦    | 男     | 95.30 ||  9 | 后羿      | 男     | 95.30 |+----+-----------+--------+-------+  select * from tb_student;+----+--------+--------+------------+| id | name   | gender | birthday   |+----+--------+--------+------------+|  1 | 李毅   | 男     | 1988-03-20 ||  2 | kevin  | 男     | 1987-08-23 ||  3 | marry  | 女     | 1989-11-25 ||  4 | lucy   | 女     | 1989-11-25 ||  5 | lily   | 女     | 1992-01-25 |+----+--------+--------+------------+  natural join：自然串連（不允許帶on/using）     natrual join：全自然串連，對左右2個表相同名字的列進行等值匹配，不可用on，using操作指定，自動刪除多餘重名列     natrual left join：左自然串連，保留2個表的列（刪除多餘重名列），以左表為準，不存在匹配的右表列，值置為NULL     natrual right join：和左自然串連相反 select * from tb_test1 natural join tb_student;+----+--------+--------+-------+------------+| id | name   | gender | score | birthday   |+----+--------+--------+-------+------------+|  1 | 李毅   | 男     | 95.30 | 1988-03-20 |+----+--------+--------+-------+------------+ select * from tb_test1 natural left join tb_student;+----+-----------+--------+-------+------------+| id | name      | gender | score | birthday   |+----+-----------+--------+-------+------------+|  1 | 李毅      | 男     | 95.30 | 1988-03-20 ||  2 | 二丫      | 女     | 95.30 | NULL       ||  3 | 張三      | 女     | 95.30 | NULL       ||  4 | 李四      | 女     | 95.30 | NULL       ||  7 | 胡魯瓦    | 男     | 95.30 | NULL       ||  9 | 后羿      | 男     | 95.30 | NULL       |+----+-----------+--------+-------+------------+ select * from tb_test1 natural right join tb_student;+----+--------+--------+------------+-------+| id | name   | gender | birthday   | score |+----+--------+--------+------------+-------+|  1 | 李毅   | 男     | 1988-03-20 | 95.30 ||  2 | kevin  | 男     | 1987-08-23 |  NULL ||  3 | marry  | 女     | 1989-11-25 |  NULL ||  4 | lucy   | 女     | 1989-11-25 |  NULL ||  5 | lily   | 女     | 1992-01-25 |  NULL |+----+--------+--------+------------+-------+  inner join：內串連（不帶on/using時做笛卡爾積）     主表和關聯表的笛卡爾積結果，通過on或者using操作篩選結果集（先求笛卡爾積，後篩選，所以效能上和where一樣），2個表的所有列都被儲存  select * from tb_student a join tb_test1 b on a.id=b.id;+----+--------+--------+------------+----+--------+--------+-------+| id | name   | gender | birthday   | id | name   | gender | score |+----+--------+--------+------------+----+--------+--------+-------+|  1 | 李毅   | 男     | 1988-03-20 |  1 | 李毅   | 男     | 95.30 ||  2 | kevin  | 男     | 1987-08-23 |  2 | 二丫   | 女     | 95.30 ||  3 | marry  | 女     | 1989-11-25 |  3 | 張三   | 女     | 95.30 ||  4 | lucy   | 女     | 1989-11-25 |  4 | 李四   | 女     | 95.30 |+----+--------+--------+------------+----+--------+--------+-------+ select * from tb_student a join tb_test1 b on a.gender=b.gender and a.name=b.name;+----+--------+--------+------------+----+--------+--------+-------+| id | name   | gender | birthday   | id | name   | gender | score |+----+--------+--------+------------+----+--------+--------+-------+|  1 | 李毅   | 男     | 1988-03-20 |  1 | 李毅   | 男     | 95.30 ||  6 | 李毅   | 男     | 1985-10-11 |  1 | 李毅   | 男     | 95.30 |+----+--------+--------+------------+----+--------+--------+-------+   left join：左外串連（必須帶on/using，等價與left outer join）     左表為主表，保留全部記錄全部列，右表為關聯表，加上on操作之外的其他列，符合on條件的記錄串連上 on和where的區別（在外串連時有差別）：on和where在效能上不存在差異，都是在笛卡爾積後篩選，但on在from階段，屬於join操作，先於where。所以當left join串連時，雖然左表的列不符合on條件，也必須保留，只是該記錄右表全是null。而where對串連後的結果做篩選，不合格直接剔除 select * from tb_student a left join tb_test1 b on a.id=b.id;+----+--------+--------+------------+------+--------+--------+-------+| id | name   | gender | birthday   | id   | name   | gender | score |+----+--------+--------+------------+------+--------+--------+-------+|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 ||  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 ||  3 | marry  | 女     | 1989-11-25 |    3 | 張三   | 女     | 95.30 ||  4 | lucy   | 女     | 1989-11-25 |    4 | 李四   | 女     | 95.30 ||  5 | lily   | 女     | 1992-01-25 | NULL | NULL   | NULL   |  NULL ||  6 | 李毅   | 男     | 1985-10-11 | NULL | NULL   | NULL   |  NULL |+----+--------+--------+------------+------+--------+--------+-------+ select * from tb_student a left join tb_test1 b on a.id=b.id and a.id<4;+----+--------+--------+------------+------+--------+--------+-------+| id | name   | gender | birthday   | id   | name   | gender | score |+----+--------+--------+------------+------+--------+--------+-------+|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 ||  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 ||  3 | marry  | 女     | 1989-11-25 |    3 | 張三   | 女     | 95.30 ||  4 | lucy   | 女     | 1989-11-25 | NULL | NULL   | NULL   |  NULL ||  5 | lily   | 女     | 1992-01-25 | NULL | NULL   | NULL   |  NULL ||  6 | 李毅   | 男     | 1985-10-11 | NULL | NULL   | NULL   |  NULL |+----+--------+--------+------------+------+--------+--------+-------+ select * from tb_student a left join tb_test1 b on a.id=b.id where a.id<4;+----+--------+--------+------------+------+--------+--------+-------+| id | name   | gender | birthday   | id   | name   | gender | score |+----+--------+--------+------------+------+--------+--------+-------+|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 ||  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 ||  3 | marry  | 女     | 1989-11-25 |    3 | 張三   | 女     | 95.30 |+----+--------+--------+------------+------+--------+--------+-------+  right join：右外串連，和左外串連相反  以後再使用MySQL的串連就清晰了。如有不準確的地方請留言指教 

MySQL的幾種串連 join/inner join/cross join/逗號/left join/right join/natural join