SQL INNER JOIN 用法解决

在表中存在至少一个匹配时，inner join 关键字返回行。

inner join 关键字语法
column_name(s)
from table_name1
inner join table_name2
on table_name1.column_name=table_name2.column_name
注释：inner join 与 join 是相同的，inner join 关键字在表中存在至少一个匹配时返回行。如果 "a" 中的行在 "b" 中没有匹配，就不会列出这些行。

多表联合查询

> mysql> create table books(     ->    bookid smallint not null primary key,     ->    booktitle varchar(60) not null,     ->    copyright year not null     -> )     -> engine=innodb; query ok, 0 rows affected (0.09 sec) mysql> mysql> mysql> insert into books values (12786, 'java',           1934),     ->                          (13331, 'mysql',          1919),     ->                          (14356, '',            1966),     ->                          (15729, 'perl',           1932),     ->                          (16284, 'oracle',         1996),     ->                          (17695, 'pl/sql',         1980),     ->                          (19264, '',     1992),     ->                          (19354, 'www.zhutiai.com', 1993); query ok, 8 rows affected (0.05 sec) records: 8  duplicates: 0  warnings: 0 mysql> mysql> mysql> create table authors(     ->    authid smallint not null primary key,     ->    authfn varchar(20),     ->    authmn varchar(20),     ->    authln varchar(20)     -> )     -> engine=innodb; query ok, 0 rows affected (0.05 sec) mysql> mysql> mysql> insert into authors values (1006, 'h', 's.', 't'),     ->                            (1007, 'j', 'c',  'o'),     ->                            (1008, 'b', null, 'e'),     ->                            (1009, 'r', 'm',  'r'),     ->                            (1010, 'j', 'k',  't'),     ->                            (1011, 'j', 'g.', 'n'),     ->                            (1012, 'a', null, 'p'),     ->                            (1013, 'a', null, 'w'),     ->                            (1014, 'n', null, 'a'); query ok, 9 rows affected (0.03 sec) records: 9  duplicates: 0  warnings: 0 mysql> mysql> mysql> create table authorbook(     ->    authid smallint not null,     ->    bookid smallint not null,     ->    primary key (authid, bookid),     ->    foreign key (authid) references authors (authid),     ->    foreign key (bookid) references books (bookid)     -> )     -> engine=innodb; query ok, 0 rows affected (0.06 sec) mysql> mysql> mysql> insert into authorbook values (1006, 14356),     ->                               (1008, 15729),     ->                               (1009, 12786),     ->                               (1010, 17695),     ->                               (1011, 15729),     ->                               (1012, 19264),     ->                               (1012, 19354),     ->                               (1014, 16284); query ok, 8 rows affected (0.05 sec) records: 8  duplicates: 0  warnings: 0 mysql> mysql> mysql> select * from authors; +--------+--------+--------+--------+ | authid | authfn | authmn | authln | +--------+--------+--------+--------+ |   1006 | h      | s.     | t      | |   1007 | j      | c      | o      | |   1008 | b      | null   | e      | |   1009 | r      | m      | r      | |   1010 | j      | k      | t      | |   1011 | j      | g.     | n      | |   1012 | a      | null   | p      | |   1013 | a      | null   | w      | |   1014 | n      | null   | a      | +--------+--------+--------+--------+ 9 rows in set (0.00 sec) mysql> select * from books; +--------+----------------+-----------+ | bookid | booktitle      | copyright | +--------+----------------+-----------+ |  12786 | java           |      1934 | |  13331 | mysql          |      1919 | |  14356 | php            |      1966 | |  15729 | perl           |      1932 | |  16284 | oracle         |      1996 | |  17695 | pl/sql         |      1980 | |  19264 | javascript     |      1992 | |  19354 | |      1993 | +--------+----------------+-----------+ 8 rows in set (0.00 sec) mysql> select * from authorbook; +--------+--------+ | authid | bookid | +--------+--------+ |   1009 |  12786 | |   1006 |  14356 | |   1008 |  15729 | |   1011 |  15729 | |   1014 |  16284 | |   1010 |  17695 | |   1012 |  19264 | |   1012 |  19354 | +--------+--------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> select booktitle, authid from books inner join authorbook; +----------------+--------+ | booktitle      | authid | +----------------+--------+ | java           |   1006 | | mysql          |   1006 | | php            |   1006 | | perl           |   1006 | | oracle         |   1006 | | pl/sql         |   1006 | | javascript     |   1006 | | |   1006 | | java           |   1008 | | mysql          |   1008 | | php            |   1008 | | perl           |   1008 | | oracle         |   1008 | | pl/sql         |   1008 | | javascript     |   1008 | | |   1008 | | java           |   1009 | | mysql          |   1009 | | php            |   1009 | | perl           |   1009 | | oracle         |   1009 | | pl/sql         |   1009 | | javascript     |   1009 | | |   1009 | | java           |   1010 | | mysql          |   1010 | | php            |   1010 | | perl           |   1010 | | oracle         |   1010 | | pl/sql         |   1010 | | javascript     |   1010 | | |   1010 | | java           |   1011 | | mysql          |   1011 | | php            |   1011 | | perl           |   1011 | | oracle         |   1011 | | pl/sql         |   1011 | | javascript     |   1011 | | |   1011 | | java           |   1012 | | mysql          |   1012 | | php            |   1012 | | perl           |   1012 | | oracle         |   1012 | | pl/sql         |   1012 | | javascript     |   1012 | | |   1012 | | java           |   1012 | | mysql          |   1012 | | php            |   1012 | | perl           |   1012 | | oracle         |   1012 | | pl/sql         |   1012 | | javascript     |   1012 | | |   1012 | | java           |   1014 | | mysql          |   1014 | | php            |   1014 | | perl           |   1014 | | oracle         |   1014 | | pl/sql         |   1014 | | javascript     |   1014 | | |   1014 | +----------------+--------+ 64 rows in set (0.00 sec) mysql> mysql> drop table authorbook; query ok, 0 rows affected (0.02 sec) mysql> drop table books; query ok, 0 rows affected (0.06 sec) mysql> drop table authors; query ok, 0 rows affected (0.03 sec)

二个表连接

mysql> select employee.first_name, job.title, duty.task
    -> from employee, job, duty
    -> where (employee.id = job.id and employee.id = duty.id);
+------------+------------+-----------+
| first_name | title      | task      |
+------------+------------+-----------+
| jason      | tester     | test      |
| alison     | accountant | calculate |
| james      | developer  | program   |
| celia      | coder      | test      |
| robert     | director   | manage    |
| linda      | mediator   | talk      |
| david      | proffessor | speak     |
| james      | programmer | shout     |
+------------+------------+-----------+
8 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> drop table duty;
query ok, 0 rows affected (0.00 sec)

mysql> drop table job;
query ok, 0 rows affected (0.01 sec)

mysql> drop table employee;
query ok, 0 rows affected (0.00 sec)

总结

inner join 连接两个数据表的用法：
select * from 表1 inner join 表2 on 表1.字段号=表2.字段号

inner join 连接三个数据表的用法：
select * from (表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号

inner join 连接四个数据表的用法：
select * from ((表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner join 表4 on member.字段号=表4.字段号

inner join 连接五个数据表的用法：
select * from (((表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner join 表4 on member.字段号=表4.字段号) inner join 表5 on member.字段号=表5.字段号