FULL JOIN

语法说明

FULL JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行。

FULL JOIN 关键字结合了 LEFT JOINRIGHT JOIN 的结果。

说明:在一些数据库中:FULL JOIN 等同于 FULL OUTER JOIN

语法结构

  1. > SELECT column_name(s)
  2. FROM table1
  3. FULL OUTER 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');
  28. mysql> select city,libname1,count(libname1) as a from t3 full join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1;
  29. +----------+--------------------+------+
  30. | city | libname1 | a |
  31. +----------+--------------------+------+
  32. | NewYork | NewYorkPublicLibra | 6 |
  33. | SanFran | SanFransiscoPublic | 1 |
  34. | Berkeley | BerkeleyPublic1 | 1 |
  35. | Berkeley | BerkeleyPublic2 | 1 |
  36. +----------+--------------------+------+