A Few Notes on the Joins Syntax in SQL

In this tutorial, we’ll provide you with several peculiarities about the joins syntax in SQL. Not just inner joins, but any type of join.

In order to fully understand the code we’ve written, you might want to take a look at the previous tutorial on that subject and our SQL course. Moreover, you can see how to download the database that we will be using in the linked tutorial.

Possible Changes in the Joins Syntax in SQL

First, we know that, in the output, we can select different columns from the tables we are referencing.

What we’ve done in our example is choose the department and employee numbers from the “dept_manager_dup” duplicate table and the department name from the “departments_dup” table.

We could have also added the starting and ending date of managers’ contracts. Let’s check for m.from_date and m.to_date.

So, we may add any combination of columns we’d like in our output. The only condition is that each column must be part of one of the joined tables.

A Different Way to Write Code

A second note I would like to share with you is that some people prefer typing the code in the FROM block first. This is because they want to specify in advance all aliases they will need in the rest of the query.

Changing the Order

For instance, they would first type the following:

Then, they will specify the selections of interest, inserting the field names with the help of the assigned aliases. So, eventually, the code will look like this:

SELECT
    m.dept_no, m.emp_no, m.from_date, m.to_date, d.dept_name
FROM
    dept_manager_dup m
        INNER JOIN
    departments_dup d ON m.dept_no = d.dept_no;

This might be helpful if you are joining more than 2 tables, but in my opinion, one can get used to remembering a few letters and follow the top-down flow of the query.

Anyway, the order to be followed is at the coder’s discretion. What matters is if the query you’ve written is correct.

Omitting the INNER Keyword

As far as the inner joins syntax in SQL is concerned, the word INNER is not obligatory. It could be omitted. In this query, whether you type INNER JOIN or just JOIN, it makes no difference.

SQL will always understand you are talking about an inner join. This is what practitioners would typically do. That’s why we will use JOIN instead of INNER JOIN.

Specifying the Matching Columns

Another issue a novice SQL programmer could encounter is whether the order that you specify the matching columns matters.

Meaning, is there any difference between typing “m.dept_no = d.dept_no” and “d.dept_no = m.dept_no”?

As you can see in the picture above, there is no difference. It is up to you. I used the M table first and the D table second to avoid confusion with the order that we designated the two tables to be joined. So, from a technical perspective, there is no difference.

The ORDER BY Clause

Let’s focus on the ORDER BY clause. We sorted the output we obtained according to the department number from the “dept_manager_dup” table. Technically, this query will work if we remove the table indication.

It will also work if we change it to the “departments_dup” table.

The point here is that it is not obvious why we use “m.dept_no” and not something else. But you will appreciate this little detail when you advance and work with much larger data sets, procedures, indexes, and other tools. Unless you use this notation when indicating the column of the “dept_manager_dup” table, SQL will display an error!

The Different Styles of Code

To sum up, there are various ways to code, which are all possible because of the joins syntax in SQL. You can decide whether or not to write the SELECT statement first or after writing FROM. You can rest your fingers by not writing an extra word, such as INNER when creating a JOIN. Moreover, you are able to specify the matching columns in whatever order you want. What you will write after the ORDER BY clause, is also your decision.

I hope that after going through these notes you feel you have joined the SQL community.

By jumping to the next tutorial, you will embark on the journey of learning what duplicate records are and how to deal with them.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Duplicate Records in SQL