All About SQL Joins

Explore the magical world of bridging data with SQL joins with a simple and comprehensive guide.

SQL encompasses a wide range of concepts, including data manipulation, data definition, transaction control, and data security. Each of these areas is crucial for effective database management and operation. However, given the relational nature of most databases, joins do stand out as a particularly vital topic within the SQL.

Let’s dive into the magical world of bridging data with SQL joins:

• Understanding SQL joins
  • CROSS JOIN
  • (INNER) JOIN
    • Equi-Join & Non-Equi / Thetha-Join
    • NATURAL JOIN
  • LEFT (OUTER) JOIN
  • RIGHT (OUTER) JOIN
  • FULL (OUTER) JOIN
  • Self-Join
• Join conditions and filtering with joins
• Best practices for working with joins
• Final thoughts

Understanding SQL Joins

SQL joins are used to combine rows from two or more tables, based on a related column between them. In simple terms, they act like bridges that ‘glue’ together data stored in different tables within a relational database. As a result, they are indispensable for reporting, analytics, and data science tasks.

There are several types of joins in SQL, each serving a different purpose and allowing for various kinds of data retrieval. They are relatively easy to understand intuitively and are usually explained with Venn Diagrams. However, since a join is essentially a Cartesian product (or a cross product) with a filter, and SQL joins operate on rows and columns, Venn Diagrams might not fully capture the nature of join operations.

In mathematics, specifically set theory, the Cartesian product of two sets A and B, denoted A × B, is the set of all ordered pairs (a, b) where a is in A and b is in B.

An illustrative example of Cartesian product is the standard 52-card deck:

• The standard playing card ranks {A, K, Q, J, 10, 9, 8, 7, 6, 5, 4, 3, 2} form a 13-element set.
• The card suits {♠, ♥, ♦️, ♣} form a four-element set.
• The Cartesian product of these sets returns 13 x 4 = 52.

The best way to visualise SQL joins is by using 'join diagrams', which represent tables with rows and columns:

Thus, SQL joins can be divided into four groups:

• CROSS JOIN
• Inner Joins: INNER JOIN and its special cases - equi-join, non-equi join, and NATURAL JOIN
• Outer Joins: LEFT JOIN, RIGHT JOIN, FULL JOIN
• Self-Join, which is a technique rather than a separate type of join, as a special case of INNER JOIN.

CROSS JOIN

CROSS JOIN returns a Cartesian product of the two tables, i.e., it joins every row of the first table with every row of the second table. For example, when you CROSS JOIN a table of 3 rows with a table of 4 rows, you will get 3×4=12 result rows.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

CROSS JOIN's ability to create a Cartesian product is particularly useful for exhaustive combination generation in various business and technical contexts. Real-world use cases of it involve:

• Product configurations: to list all possible combinations of product features.
• Scheduling resources or planning events by matching every time slot with every resource or participant to explore all possible scheduling combinations.
• Marketing and promotional campaigns: to pair products with various marketing channels or promotional strategies to explore all potential outreach combinations.
• Generate all possible input combinations for test cases.

All other joins are still based on cross joins, but with additional filters, and perhaps unions.

(INNER) JOIN

INNER JOIN or JOIN returns records that have matching values in both tables. In plain text, an INNER JOIN is a CROSS JOIN in which only those combinations are retained which fulfil a given predicate. It's the most common type of join.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Equi-Join & Non-Equi / Thetha-Join

An INNER JOIN can use any comparison operator in the join condition, such as =, <, >, <=, >=, or <>, to combine rows from two or more tables based on a related column between them.

An equi-join is a type of SQL join where you specifically use the equality operator (=) to match columns from two tables. The primary difference between INNER JOIN and equi-join lies in the terminology and the specificity of the condition used for joining tables. In practice, when an equi-join is used as an INNER JOIN with an equality condition, there's no practical difference in syntax or performance. The term "equi-join" just emphasises that the join condition is based on equality.

When a join uses comparison operators like <, >, <=, >=, or <> to combine rows from two or more tables, it is typically referred to as a non-equi join or theta-join. In SQL, these types of joins allow for more complex conditions beyond simple equality, enabling a broader range of queries to be expressed.

Thus, equi-join and thetha-join are special cases of INNER JOIN.

NATURAL JOIN

NATURAL JOIN automatically performs a join between two tables based on columns with the same names and compatible data types in both tables. It implicitly uses all the columns with the same names across both tables for the join condition.

Syntax:

SELECT columns
FROM table1
NATURAL JOIN table2;

It's important to use this join with caution because the join operation is based on column names automatically. If the tables change over time (e.g., adding a new column with the same name in both tables), it could lead to unexpected results.

LEFT (OUTER) JOIN

LEFT OUTER JOIN or LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT (OUTER) JOIN

RIGHT OUTER JOIN or RIGHT JOIN returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL (OUTER) JOIN

FULL OUTER JOIN or FULL JOIN returns all records when there is a match in either left (table1) or right (table2) table records. Essentially, it combines the results of both LEFT JOIN and RIGHT JOIN.

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Self-join

A join does not always have to involve two different tables. You can join a table to itself, creating a self-join. This might seem a bit odd at first, but it's quite useful when you want to compare values in a column to other values in the same column.

Self-join is not a particular type of join in SQL. This means joining the table with itself works with any join: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN.

One important note is that giving table aliases is mandatory here. As we join a table with itself, we have to give different aliases so the query knows which table the columns in SELECT and ON are from. Basically, you’re doing the regular join, but you’re treating one table like these are two distinct tables in join.

In the example above, the result of the query gives us a clear, row-by-row representation of each employee and their manager (manager-employee relationships), demonstrating how self-join allows us to compare and relate rows within the same table.

Other use cases of self-join:

• Representing hierarchical data: Self-joins can visualise the organisational structure or find the chain of command. In manufacturing they can help in understanding part hierarchies and the composition of final products.
• Finding pairs of employees working in the same department.
• Comparing product features or prices: If you have a table listing various products, you might use a self-join to compare products within the same category, such as finding products that are more expensive than the average price within their category.
• Identifying overlapping events in a table where events are listed with start and end times.
• In a table with geographical locations, a self-join can be used to find locations within a certain distance of each other, useful for services needing to calculate proximity.

Join Conditions & Filtering with Joins

• ON Clause: Specifies the column(s) that the join is based on. Used with most types of joins.
• USING Clause: An alternative to the ON clause, used when both tables have the same column names that you're joining on.
  Syntax: USING(column_name).
• WHERE Clause: After specifying the join, you can use a WHERE clause to filter the results further.

Best Practices for Working with Joins

Knowing how to efficiently use joins can significantly impact the performance of SQL queries. Optimising join operations is a key skill for database professionals to ensure fast and efficient data retrieval.

Here are some best practices to consider when working with SQL joins:

• Prefer explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.) over implicit syntax (selecting from multiple tables with a comma-separated list and using WHERE for conditions). Explicit syntax is clearer, more readable, and less prone to errors.
• Use aliases for table names to make your SQL queries more readable and easier to manage, especially when dealing with multiple joins or self-joins.
• Optimise join conditions: Ensure that join conditions are on indexed columns where possible to speed up the join operation. This is particularly important for large tables.
• Understand how different join types handle NULL values. For example, an OUTER JOIN will include rows with NULL in the join column from one of the tables, which might not be desired in all cases.
• Select only the columns you need rather than using SELECT *. This minimises the amount of data that needs to be processed and transferred, improving performance.
• Filter early: Apply WHERE clauses before joining tables if possible, to reduce the number of rows that need to be joined. This can significantly improve query performance.
• Be careful with CROSS JOINs as they can produce a very large number of rows by combining every row of one table with every row of another.
• In some cases, using a subquery instead of a join can simplify the query and improve performance, especially if the subquery can be executed once rather than for each row of the main query.
• Use query execution plans to analyse the performance of your joins. Look for opportunities to add indexes or refactor queries to make them more efficient.

Following these best practices can help ensure that your SQL joins are efficient, maintainable, and effective in retrieving the data you need.

Final Thoughts

Concluding our journey through the landscape of SQL joins, it's clear that mastering joins is not just about understanding the syntax; it's about grasping the intricate data relationships within a relational database.

In the world of SQL, where data is the lifeblood of applications, websites, and business intelligence, joins are an essential skill for any database professional, developer, or data analyst. Experimenting with different join types and exploring various optimisation techniques is crucial for developing SQL skills. Keep practicing, keep learning, and let the magic of SQL joins illuminate your path to data mastery.

REFERENCES

1. Cartesian product, from Wikipedia.
2. Self-joins, from IBM Informix Servers 14.10 Documentation.
3. Joel Murach, Murach’s MySQL. Training & reference. First Edition (Fresno, CA: Mike Murach & Associates, Incorporated, 2012).