SQL 連結関連

PHP SQL
  • 左外部連結
select books.asin, booktags.tagid
from books
left outer join booktags on books.id = booktags.bookid;

+------------+-------+
| asin       | tagid |
+------------+-------+
| 4274066304 |  NULL |
| 4873112915 |     1 |
| 4479791779 |  NULL |
| 4479791779 |  NULL |
| 4873112990 |     3 |
| 4873112990 |    10 |
| 4839923205 |     1 |
| 4839923205 |     3 |
| 4839923205 |     2 |
| 4797340185 |  NULL |
| 4798100234 |  NULL |
| 4334033857 |  NULL |
| 4480422536 |  NULL |
| 4797339039 |     8 |
| 4797339039 |     9 |
| 4334034020 |  NULL |
| 4873113202 |     1 |
| 4873113202 |     2 |
| 4873113202 |     3 |
| 4873113202 |    12 |
| 4166605461 |  NULL |
| 4576060732 |     2 |
| 4576060732 |    11 |
+------------+-------+
  • ３連結
select books.*, tags.tag
from books
left outer join booktags on books.id = booktags.bookid
left outer join tags on booktags.tagid = tags.id;

select books.id, books.asin, tags.tag
from books
left outer join booktags on books.id = booktags.bookid
left outer join tags on booktags.tagid = tags.id;

+----+------------+-----------+
| id | asin       | tag       |
+----+------------+-----------+
| 57 | 4274066304 | NULL      |
| 68 | 4873112915 | ruby      |
| 78 | 4479791779 | NULL      |
| 61 | 4479791779 | NULL      |
| 77 | 4873112990 | magazine  |
| 77 | 4873112990 | microsoft |
| 94 | 4839923205 | ruby      |
| 94 | 4839923205 | magazine  |
| 94 | 4839923205 | rubyist   |
| 80 | 4797340185 | NULL      |
| 75 | 4798100234 | NULL      |
| 81 | 4334033857 | NULL      |
| 82 | 4480422536 | NULL      |
| 83 | 4797339039 | youtube   |
| 83 | 4797339039 | NULL      |
| 84 | 4334034020 | NULL      |
| 85 | 4873113202 | ruby      |
| 85 | 4873113202 | rubyist   |
| 85 | 4873113202 | magazine  |
| 85 | 4873113202 | JAVA      |
| 92 | 4166605461 | NULL      |
| 88 | 4576060732 | rubyist   |
| 88 | 4576060732 | GTD       |
+----+------------+-----------+

select tags.tag, books.id, books.title, books.peta_cnt
from tags
left outer join booktags on tags.id = booktags.tagid
left outer join books on booktags.bookid = books.id;
  • 連結＋条件
select tags.tag, books.id, books.title, books.peta_cnt
from tags
left outer join booktags on tags.id = booktags.tagid
left outer join books on booktags.bookid = books.id
where tags.tag = 'ruby';