INNER JOIN

语法说明

INNER JOIN 选取两个表中具有匹配值的数据记录。（等同于JOIN）

语法结构

1. > SELECT column_name(s)
2. FROM table1
3. INNER JOIN table2
4. ON table1.column_name = table2.column_name;

参数释义

示例

1. > drop table if exists t1,t2,t3;
2. > create table t1 (libname1 varchar(21) not null primary key, city varchar(20));
3. > create table t2 (isbn2 varchar(21) not null primary key, author varchar(20), title varchar(60));
4. > create table t3 (isbn3 varchar(21) not null, libname3 varchar(21) not null, quantity int);
5. > insert into t2 values ('001','Daffy','Aducklife');
6. > insert into t2 values ('002','Bugs','Arabbitlife');
7. > insert into t2 values ('003','Cowboy','Lifeontherange');
8. > insert into t2 values ('000','Anonymous','Wannabuythisbook?');
9. > insert into t2 values ('004','BestSeller','OneHeckuvabook');
10. > insert into t2 values ('005','EveryoneBuys','Thisverybook');
11. > insert into t2 values ('006','SanFran','Itisasanfranlifestyle');
12. > insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
13. > insert into t3 values ('000','NewYorkPublicLibra',1);
14. > insert into t3 values ('001','NewYorkPublicLibra',2);
15. > insert into t3 values ('002','NewYorkPublicLibra',3);
16. > insert into t3 values ('003','NewYorkPublicLibra',4);
17. > insert into t3 values ('004','NewYorkPublicLibra',5);
18. > insert into t3 values ('005','NewYorkPublicLibra',6);
19. > insert into t3 values ('006','SanFransiscoPublic',5);
20. > insert into t3 values ('007','BerkeleyPublic1',3);
21. > insert into t3 values ('007','BerkeleyPublic2',3);
22. > insert into t3 values ('001','NYC Lib',8);
23. > insert into t1 values ('NewYorkPublicLibra','NewYork');
24. > insert into t1 values ('SanFransiscoPublic','SanFran');
25. > insert into t1 values ('BerkeleyPublic1','Berkeley');
26. > insert into t1 values ('BerkeleyPublic2','Berkeley');
27. > insert into t1 values ('NYCLib','NewYork');

29. > select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1;

31. +----------+--------------------+------+
32. | city     | libname1           | a    |
33. +----------+--------------------+------+
34. | NewYork  | NewYorkPublicLibra |    6 |
35. | SanFran  | SanFransiscoPublic |    1 |
36. | Berkeley | BerkeleyPublic1    |    1 |
37. | Berkeley | BerkeleyPublic2    |    1 |
38. +----------+--------------------+------+

限制

目前， INNER JOIN语句在进行某些查询时将导致崩溃（与服务器失去连接）。