There are many ways to JOIN data from two database tables and filter the information you require. Craig Buckler wrote a popular piece on understanding JOINs; namely INNER, LEFT, RIGHT, and FULL OUTER. This article is an extension of that one.
Let’s recap these real quick. Picture two tables, one for customers and one for books to establish a book loan database.
customers table
| id | firstname | lastname | book_id |
|---|---|---|---|
| 1 | Joe | Blow | 1 |
| 2 | Jane | Doe | 2 |
| 3 | Harry | Crow | 2 |
| 4 | Jeffrey | Snow | 0 |
books table
| id | Title |
|---|---|
| 1 | Star Wars |
| 2 | Jurassic Park |
| 3 | Little Women |
| 4 | Tom Sawyer |
The books table has one row for every book.
The customers table has one row for each customer who can only have one book on loan at a time. If they have no book on loan, the book_id would be 0 or an empty string.
This is a very simple example to make the JOINs as clear as possible to understand!
A LEFT JOIN here would be in the case you want to ask a question such as “show me all customers including any books on loan.”
You can see in the image that ALL data in the left circle, or table, is included in the result set. Only data that overlaps from the books table is included from the right table. This means that with a LEFT JOIN, some data in the right table may be excluded.
A RIGHT JOIN would be like asking “show me all the books in my library, along with any customers that have borrowed them.”
Continue reading %3 More Joins You Should Be Familiar With%
by Zack Wallace via SitePoint
