• Home
  • blog
  • 3 More Joins You Should Be Familiar With

3 More Joins You Should Be Familiar With

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


books table

1Star Wars
2Jurassic Park
3Little Women
4Tom 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.

Left Join Venn Diagram

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%