Mastering your SQL commands (Part 2)

I am Kingsley Ubah, a budding software developer and tech enthusiast. Today, I am going to write about Inner Joins and Outer Joins. This is the second of the three posts that comprises the Mastering your SQL commands series.

In my last post, I discussed the basics of SQL including the clauses and verbs used to form the typical database queries. I created a hypothetical book inventory database which I used to illustrate the basic database operations. Today, however, we are going to discuss a more complex form of database query.
Let’s bring in our hypothetical database, BookInventory. If you recall, it had four tables: Names, Editions, New_Books and Used_Books. Now, our former operations against that database entailed retrieving, adding or deleting records from just one table at a time. But what if you wanted to retrieve related records from two or more tables at the same time? This is when Inner Joins comes into the equation.

Inner Joins

With Inner Joins, you can take two or more tables and combine their rows based on a commonly shared value – a common key – for each row. When you perform an inner join in a SELECT query, you simply specify the tables to be
joined in the FROM clause and also indicate what columns they have in common. This is the typical syntax for such query:

SELECT [column names] FROM [table1] INNER JOIN [table2] ON [table1.column] =[table2.column]

Now, from the context of our BookInventory database, we can run the following query to retrieve the edition, color and editionyear for a new book based on a common id:

SELECT Editions.edition, New_Books.color, New_Books.editionyear FROM Editions
INNER JOIN New_Books ON Editions.name_id = New_Books.book_id;

edition	color	modelyear
1st	Red	2012
5th	Radiant Red	2015
6th	Orange	2018

Notice that in the SELECT query, each of the columns in the list is prefixed with the name of the table they belong to. This is a good practice. It is usually a good idea to help avoid conflicts between identically named columns.

If you are like me and happen to like brevity in your SQL queries, there’s a simple method for you to shorten all of those table prefixes. In the FROM clause, you can specify a table alias for each table called, like so:

SELECT [column names] FROM [table1] alias1 INNER JOIN [table2] alias2 ON [alias1.column] = [alias2.column]

To illustrate:

SELECT e.edition, n.color, n.editionyear FROM Editions e
INNER JOIN New_Books n ON e.name_id = n.book_id;
See? Very simple!

If the common keys/ column linking the Editions and New_Books tables are exactly the same, i.e both have either a name_id column or book_id column (which is the case here), we can use the following query to perform the same action:

SELECT e.edition, n.color, n.editionyear FROM Editions e
INNER JOIN New_Books n USING (book_id);

Note that In some situations, you will need to join more than two tables together in a query. MySQL can handle this easy as well—all you have to do is chain your joins next to each other in the order you want them evaluated.
In the book inventory database, you’ll need to use multiple joins to return the name of each book with its record, like so:

SELECT na.name, ed.edition, n.color, n.editionyear FROM Names ma INNER JOIN
Editions ed USING (book_id) INNER JOIN New_Vehicles n USING (book_id);

When you run this query, you should see the following:

name	edition	color	modelyear
Javascript	1st	Red	2012
PHP	5th	Radiant Red	2015
Python	6th	Orange	2018

Outer Joins

While most complex linking can be achieved using inner joins, there are some situations where they are
limited. For instance, if a database row does not have a value defined for the column being linked, it will be ignored by inner joins. In the book inventory example, every time you queried the table using an inner join to
link the Editions table, only three rows were returned. The fourth book in the New_Books table was ignored, because its value for book_id was null. What you need is a way to return all rows, whether or not there is a book_id specified, and if a book_id is specified, perform the requested joins. For such a task, MySQL provides outer joins.

In the book inventory example, if you wanted to return a set of rows where all new vehicles were returned, regardless of their book_id, you might use the following:

SELECT ed.edition, n.color, n.editionyear FROM New_Books n LEFT OUTER JOIN Editions ed USING (book_id);

edition	color	modelyear
1st	Red	2012
NULL	Dark Brown	2008
5th	Radiant Red	2015
6th	Orange	2018

With this, I have come to the end of this topic. A third post on this series will be released soon. Until then, follow me and stay updated.

Follow me on twitter at UbahTheBuilder

Later