RIGHT JOIN

Description

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is NULL records from the left side, if there is no match.

note

In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Syntax

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

Examples

  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 right 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. +----------+--------------------+------+