A Few Notes on the Joins Syntax in SQL

Join over 2 million students who advanced their careers with 365 Data Science. Learn from instructors who have worked at Meta, Spotify, Google, IKEA, Netflix, and Coca-Cola and master Python, SQL, Excel, machine learning, data analysis, AI fundamentals, and more.

Start for Free
Martin Ganchev 25 Apr 2023 7 min read

Above few of man typing on keyboard, 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.

department and employee numbers from Dept_manager_dup and department name from departments_dup, joins syntax in sql

We could have also added the starting and ending date of managers’ contracts. Let’s check for m.from_date and m.to_date. from_date and to_date from dept_manager dup, joins syntax in sql

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: From inner join

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:

    m.dept_no, m.emp_no, m.from_date, m.to_date, d.dept_name
    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.

Top down flow, joins syntax in sql

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.

Inner join = join, joins syntax in sql

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”? No difference

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. Order by clause

It will also work if we change it to the “departments_dup” table. With 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! use m.dept_no

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

Martin Ganchev

Instructor at 365 Data Science

Martin holds an MSc degree in Economic and Social Sciences from Bocconi University. His diverse academic and research experience combined with his friendly and explanatory approach to teaching have made him one of the most beloved instructors on our team. Some of the courses he has authored include: SQL, SQL + Tableau, SQL+Tableau+Python, Introduction to Python, Introduction to Jupyter, to name a few.