5 Best Practices for Writing SQL JOINs

These best practices for SQL JOINs will help you make readable SQL queries that deliver accurate, clear, and understandable output.

Considering that relational databases are built around related tables, it’s no wonder that SQL JOINs – which allow you to combine information from multiple tables – are one of the primary skills acquired by SQL newbies. SQL JOINs course by LearnSQL.com is the perfect place to refresh your knowledge about JOINs. If you want your SQL queries to work and be professional-looking, you need to follow SQL JOINs best practices. I’ve summarized some of them in this article.

SQL JOINs Overview

Let’s say you want to see all orders from a set of customers along with their full names. Sounds pretty simple, right? However, in practice, you are not likely to find information on customers and orders in the same table. The orders table may include a customer ID for each order, but usually nothing more on the customers.

To combine information from the customers and orders tables, you’ll need to join these tables based on a common column value (i.e. the customer ID number). Here’s how your SQL query may look if you want to see all columns from both tables:

SELECT *
FROM customers
JOIN orders
ON customers.id = orders.customer_id;

In the SELECT clause, you specify which columns you want to see in the output. Then, you put the name of one table in the FROM clause and the name of another table in the JOIN clause. Finally, in the ON clause, you specify which columns from each table should be used for joining.

You can practice SQL JOIN syntax with our  SQL JOINs interactive course, which includes 93 coding challenges. If you are new to SQL JOINs, check out my step-by-step guide to joining tables in SQL.

Before going through the SQL JOIN best practices, I want to remind you briefly about the different types of SQL JOINs:

• INNER JOIN (or simple JOIN) is the basic join type. It’s used to display matching records from both tables.
• LEFT JOIN (or LEFT OUTER JOIN) is applied to keep all records from the left table and only matched records from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN) is used to keep all the records from the right table and only matched records from the left table.
• FULL JOIN (or FULL OUTER JOIN) is used to keep all records from all tables, even unmatched ones.
• CROSS JOIN returns a result set with all possible combinations of the rows from two tables.

Here's my article with examples of different SQL JOIN types.

SQL JOIN is a powerful tool that goes well beyond the simple joining of two tables using one common column. You can use different types of SQL JOINs for joining a table with itself (self-join); joining tables without a common column; joining tables using conditional operators other than equals (non-equi joins); joining multiple tables (more than two) in one query, or joining tables based on several columns.

Now, let’s see what the best SQL practices when joining two or more tables.

5 SQL JOINs Best Practices

If you want your SQL queries to be clear and readable – and, most importantly, return the expected output – you need to follow certain practices for writing SQL JOINs.

1. Use the JOIN and ON Keywords

First of all, it is highly recommended to use explicit joins, i.e. by using the JOIN and ON keywords. You can sometimes encounter SQL queries where tables are joined implicitly by simply listing table names in the FROM clause and using the WHERE clause to specify the join condition.

For example, the following two queries will give the same output:

SELECT *
FROM customers
JOIN orders
ON customers.id = orders.customer_id;
SELECT *
FROM customers, orders
WHERE customers.id = orders.customer_id;

However, only the first of these queries follows JOIN best practices. The second query, which has the implicit join, has several issues:

• It is less readable; this gets worse with complex SQL queries. You have to look closely to establish the tables that are being joined. You also have to find the join condition, which doesn’t have a standard ON keyword before it.
• You cannot specify the JOIN type when using an implicit JOIN (e.g. FROM customers, orders). This can be a problem if you need to use something beyond the standard INNER JOIN.
• Defining a join condition in the WHERE clause can be very confusing, since this is not the clause’s typical use. WHERE is used to filter data; a query is hard to understand when join conditions are mixed with filtering conditions in one clause.

So, use JOIN to specify the tables to join and ON to specify the join condition.

2. Choose Appropriate SQL JOIN Type

Before writing an SQL query, think carefully about which type of SQL JOIN is the most appropriate in this particular case.

If you are joining customers with a list of completed orders, do you want to include customers that don't have completed orders yet? Do you want to include orders that don't have the corresponding customer ID for some reason? Depending on your answers, you may need to use LEFT JOIN, RIGHT JOIN, or even FULL JOIN.

Make sure you choose the appropriate SQL JOIN type and put the table names in the correct order when using LEFT JOIN or RIGHT JOIN.

3. Carefully Design the JOIN Condition

If you are joining two tables using their respective primary key and foreign key, the join condition is usually quite straightforward. However, this is not always the case:

• One common column may not uniquely identify the records, in which case you’ll need to use two or more join conditions. For example, if you have multiple people with the same last name in your database, you cannot use just the last name to join tables. You’ll need to look for another column (e.g. ID) or a combination of columns. Here’s how to join two tables using a combination of two columns (first_name and last_name) in both tables:

  SELECT 
  s.first_name, 
  s.last_name, 
  u.id
  FROM students s
  JOIN users u
  ON s.first_name = u.first_name 
  AND s.last_name = u.last_name;
  

• You may need to apply non-equi joins with conditional operators beyond the equals. If you’re checking that a house price is within buyers’ price expectations when joining potential buyers with houses for sale, your query would look something like this:

  SELECT 
  	b.id, 
  	b.name, 
  	h.id, 
  	h.address, 
  	h.price
  FROM buyers b
  JOIN houses h
  	ON h.price BETWEEN b.min_price AND b.max_price;
  

• There can be multiple corresponding records in another table (e.g. one customer may have many orders). Do you want to get all of these orders or just the most recent? If you want to join only the most recent order, your query will be slightly more complex:

  SELECT 
  	customers.id, 
  	customers.first_name, 
  	customers.last_name,
  	orders.order_date, 
  	orders.order_status
  FROM customers
  JOIN orders
      ON orders.id = (
          SELECT TOP 1 id
          FROM orders
          WHERE customer_id = customers.id
          ORDER BY order_date DESC
      )
  ORDER BY customer_id;
  

Defining a correct join condition is crucial for getting the correct output after joining tables in SQL.

4. Use Table Aliases

It is a good practice to use table aliases when joining multiple tables or when joining tables with long names. Usually, the alias simply includes the first letter of the full table name or two and more letters if the table name consists of several words (e.g. c for customers, ds for daily_sales, etc.).

Returning to our example, we can use the following query to get information on the customer’s ID, first name, last name, order ID, order date, and order status:

SELECT
	c.id,
	c.first_name,
	c.last_name,
	o.id,
	o.date,
	o.status
FROM customers c
JOIN orders o
	ON c.id = o.customer_id;

Note that we define the table aliases in the FROM and JOIN clauses right after the full table name. Then, we use these aliases to refer to specific columns of these tables in the SELECT and ON statements. This can save us a lot of writing, especially if table names are long.

5. Use Column Aliases

Column aliases are the names that we assign to the columns in the result set. By default, the output columns will have the same names as the corresponding columns in the original tables. However, this often results in confusing output.

For instance, the output of the query from our example above will include two columns with the name id that correspond to the customer ID and order ID columns.

You can follow the order of columns to understand which one is which, but the better approach is to use column aliases:

SELECT
	c.id AS customer_id, 
	c.first_name, 
	c.last_name, 
	o.id AS order_id, 
	o.date AS order_date, 
	o.status AS order_status.
FROM customers c
JOIN orders o
	ON c.id = o.customer_id;

Note that I am also using column aliases for the columns with order date and order status. In the resulting table, it is not clear what these dates and statuses are about. To avoid any confusion, it's better to specify column names explicitly using the column aliases. Check out this article for more SQL JOIN tips.

Time to Practice SQL JOINs!

SQL JOIN is one of the primary skills acquired by those who start working with relational databases. However, there’s much more than simple joins involved. There are many advanced concepts under the SQL JOINs umbrella, including different types of outer joins, self-joins, non-equi joins, and more.

It’s important to know and use SQL JOINs best practices from the start:

• Define your SQL JOINs explicitly with JOIN and ON keywords.
• Make sure to select the correct SQL JOIN type.
• Carefully design the join condition.
• Use table aliases, especially with long table names.
• Use column aliases to make the query output more understandable.

Interactive courses are usually the best approach for practicing newly acquired skills. If you are very new to SQL, I recommend starting with the SQL Basics course. It will introduce you to key SQL topics, including SQL JOINs.

To get a deeper understanding of different JOIN types, check out the SQL JOINs course. It covers INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN; filtering data with different kinds of JOINs; joining a table with itself; joining tables on non-key columns, and more.

Did you enjoy this article? We publish a lot of useful guides and hands-on tutorials.

Thanks for reading, and happy learning!