Joins in SQL

In SQL there are several ways to view data. One of these ways is joins. There are four different kinds of joins. This is the data we’ll be using to demonstrate joins. Two tables of Book and Author. Book has a foreign key to the primary key of Author, binding them together. Using joins, we can see data which is similar between the two or different.

Inner Join

An inner join is used to have the result table display the records from the left and right table which have a common field between them. Anything that doesn’t match won’t be returned.

select book.book_id, author.first_name 
from book
inner join author 
on book.author_id = author.author_id;

Our result has fetched us only the books which have a corresponding author_id which exists in the author table. Note that books 13-15 have a null author, so we do not see them in our result set.

Left Join

A left join is used to produce a resulting table that has records from both tables that match as well as the values that exist solely in the left table.

Unlike the inner join, our result set for the left join has all of our books in it, even the ones which don’t have an actual value for author_id. Why? Because the left join is returning the values from the left table-which is books in this case, as well as the values that existed in our inner join. It ignores any values from the right table which don’t match the left table. In this case, all our authors do have at least one book in the system, but it can become much more noticeable with larger databases.

Right Join

A right join is the opposite of a left join; it will return the records from both tables that match as well as the right table’s records.

In our results, we’ve fetched the records that matched between the tables, 1-12 of our books as well as a null book_id for H.P. Lovecraft from our right table, our Author table. He only showed up here because he didn’t have a book for the left join or inner join, but he appears for the right join because he is in the right table only.

Full Join

A full join will return records from both tables, regardless of any common fields between them. It is like combining a left and right join.

*whistles* That’s one large table! That is because it’s encapsulating all of our records from both tables, without worrying about the foreign key between the two tables. We’re getting H.P. Lovecraft despite him not having any books in the book table and we’re getting the three books which don’t have an author in the author table as well as all of the other books which have authors properly.

A full SQL file for creating the database used in this blog can be found on my Github here: https://github.com/salkiduckyJUMP/sql-demos/blob/master/Joins%20Example%20Blog%20Demo.sql

Further information on joins can be found here: http://www.sql-join.com/sql-join-types

Venn Diagrams sourced from https://www.w3schools.com/sql/sql_join.asp