SQL Joins

In this blog post, I will do a short walk-through on the use of the SQL join clause and its types to retrieve related data between two tables on a simple blogging database. Come and join the venture beyond SQL.

What is a SQL join?

A JOIN clause is used to combine records or rows between one or more tables through a related column that references another record. This way, related records can be retrieved through a single query. You might have encountered this clause, as it is commonly used if not, this is an opportune moment.

Now, there are ways to combine these records accordingly through join clause types. There are cases where there are no related records between joined tables yet the result can be controlled.

Each of the types will be tackled in a simple manner using two tables with a one-to-many relationship (a user has many blogs), as shown in the entity relationship diagram generated through MySQL workbench.

The blogs table references the id of the user through the field user_id (nullable foreign key), which is the id (primary key) of the users table. Both of these tables have three stored records for demo purposes.

Inner Join

When doing an inner join, the query results are limited to matching records between the two tables otherwise, the result will be empty. This join type is ideal for aggregating related data if the condition is satisfied.

The query below manifests combined results due to the matching ID from the user table being referenced by the blog table. One record is left out due to a non-matching fields.

Left Join

A left (outer) join includes all the records from the left table (blogs), even if there are no matching records from the right table (users). Use this join type if you still want to show data from the left table even if the condition is not satisfied. This is great way to show results to the user when there are optional filters.

The non-matching record from the right gets replaced with null or empty values, yet the left table still showed a blog record.

Right Join

A right (outer) join is functionally the same as its counter-part which is the left join. It will exclude data from the left table instead. Ideal for fine-grained results where you may still want access to the user data despite having no posted blogs.

The fields from the left table have been replaced with null, and the data from the right table is included in the results.

Full Join

A full (outer) join returns results from either the left or right table with or without matching records. The FULL JOIN clause is not supported on MySQL out of the box but can still be achieved by combining the results from the left and right queries above through a UNION clause.

Simply take the distinct result or records with null values from the previous left and right join query, and then you have a full outer join result.

How’s the short venture?

So far, joins are a vital part of relational databases. It is widely used and a convenient way to collect related or validate non-related records. I hope you took a sip from your trusty canteen after this short venture to stay hydrated. Until next time, have a great day ahead.