SQL Joins Explained In <5 Minutes [4 Step-by-Step Examples]
Contents
SQL Joins make new tables by retrieving data from other tables.
The relationship between tables is expressed (or described) as a type of SQL join.
This article gives you a clear, step-by-step introduction to the four different SQL join types.
SQL Join Types
There are four useful join types in SQL.
- Inner Joins: Find records where the values match in both tables
- Left Outer Joins: Return all records from the left table and only records from the right table that match.
- Right Outer Joins: Return all records from the right table and only records from the right table that match.
- Full Outer Joins: Return all records from the left and right tables.
SQL Joins can find matching rows in two or more tables.
Let’s go over an example of how to extract data using joins.
Want To Follow Along?
Jump to the appendix using this link – appendix here – to find out how to grab our test data and follow along!
Extracting Our Data using Join Type Expressions
This section will use the four SQL join expressions to see how we can use them to extract data from these two tables.
Which Table Is Left And Which Is Right In A SQL Join?
Left and Right refer to where a table resides in the FROM clause of the join clause that you will be using.
The left table is the table in the FROM clause and the one on the left of the SQL join clause you are using.
The right table is the one on the right of the SQL join clause.
Inner Join
With a SQL inner join, we are saying: “extract all books that have matching publishers”.
Visually, we would express this as a Venn diagram:
select * from tbl_books INNER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this INNER JOIN example extracts the 25 books with matching publishers.
Left Outer Join
With a left outer join, we are saying: “extract all books that have and those that have not got matching publishers”.
Visually, we would express this as a Venn diagram:
The SQL to carry out this is
select * from tbl_books LEFT OUTER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this LEFT OUTER JOIN example extracts the 26 books that have matching publishers and also includes the one book that had a non-matching tbl_publisher.publisher_id
Right Outer Join
With a right outer join, we are saying: “extract all publishers that have and have not got matching books”.
Visually, we would express this as a Venn diagram:
select * from tbl_books RIGHT OUTER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this RIGHT OUTER JOIN example extracts the 29 publishers that have matching books and also includes the four publishers that had a non-matching tbl_books.publisher_id
Full Outer Join
With a full outer join, we are saying: “extract all publishers and books regardless of whether they have matching publisher_id’s”.
Visually, we would express this as a Venn diagram:
select * from tbl_books FULL OUTER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this FULL OUTER JOIN example extracts the 30 books with matching publishers.
It also includes the one book that did not have a matching tbl_publisher.publisher_id and also consists of the four publishers that had a non-matching tbl_books.publisher_id
If you are curious how joins can be used in your own workplace, see our SQL courses to learn more!
Expressing A SQL Join Type
The SQL join type between two tables is expressed via the relationship that each table has with another SQL table via its primary (PK) or foreign (FK) keys.
For example, we may have two related tables whose relationship is expressed as follows:
“One publisher can publish Many books”
Note that here we are also saying that “One book cannot have Many publishers”
Remember that the primary key is always the One side of a relationship. The FK is always the Many side of the relationship.
See this article to learn more about primary keys and foreign keys.
Conclusion
SQL Joins are fundamental to working with relational databases.
You will need to master them early on as you develop your skills as a data analyst or database administrator so go and give them a try today!
If you want to keep learning SQL but don’t have much free time, you can try the SQL learning apps available through your iPhone or Android phone’s app stores.
You’ll be able to learn the theory and put it into practice by completing exercises.
Appendix – Example Data
To demonstrate the different join types in SQL, we will use some simple data.
tbl_publisher
This table has a total of 18 publishers.
Four publishers have not published any books – 15, 16, 17, 18 – we see more about this below.
tbl_books
This table has a total of 25 books.
There is an error in our data as there is one book with a publisher_id of 0 that does not exist.
There are no books that match for four of our publisher_id’s 15, 16, 17, 18
The relationship between the publisher and books table is visualised below.
As you can see, “One publisher can publish Many books”.
This shows that the relationship we need to use is between the (PK) tbl_publisher.publisher_id and (FK) tbl_books.publisher_id
We could express this as follows:
select * from tbl_books, tbl_publisher
where tbl_books.publisher_id = tbl_publisher.publisher_id
However, this is better expressed using SQL join type expressions.
Note: If you are using a live database rather than example data, please see this article to make sure your database is fully backed up.
