SQL SERVER – Modern Explicit JOIN Syntax – A Brief Note

In the world of SQL, JOIN operations are indispensable. They allow us to combine rows from two or more tables based on a related column between them, enabling us to create more complex queries and extract more meaningful insights from our data. Over time, SQL has evolved and offers two distinct styles for performing JOIN operations: the old-style comma-separated join and the more modern explicit JOIN syntax.

Below, we will delve into the unique features of both styles and explore why the modern JOIN syntax has increasingly become the preferred choice for SQL programmers.

Traditional Comma-Separated JOIN

The old-style comma-separated JOIN, an implicit join, is a straightforward and intuitive method for joining tables. Here is an example:

SELECT column1, column2
FROM table1, table2
WHERE table1.id = table2.table1_id;

In this approach, the tables to be joined are listed in the FROM clause, separated by a comma. The JOIN condition is then specified in the WHERE clause. This syntax will perform an inner join, combining rows from both tables where the join condition is met.

Modern Explicit JOIN Syntax

The modern method for joining tables is through the explicit JOIN syntax. It is more verbose but also more expressive and flexible, as shown below:

SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.id = table2.table1_id;

In this syntax, the type of join (e.g., INNER JOIN) is clearly identified, and the join condition is specified in its own ON clause. This syntax also allows various types of joins like LEFT JOIN, RIGHT JOIN, FULL JOIN, and more, each serving a unique purpose.

Why is the Modern JOIN Syntax Better?

Now that we understand the basic syntax of each style, let’s discuss why the explicit JOIN syntax is generally preferred over the traditional comma-separated method.

1. Clear Separation of JOIN Conditions and Filters

In the old-style JOIN, the join conditions are mixed with other filtering conditions in the WHERE clause. This can make complex queries harder to understand. On the other hand, the explicit JOIN syntax separates the join conditions (in the ON clause) from the filtering conditions (in the WHERE clause), improving readability and maintainability.

2. Explicit Definition of JOIN Type

With the comma-separated syntax, the default JOIN operation is a cross-join, which can result in large result sets and potential performance issues. On the other hand, the explicit JOIN syntax allows you to specify the type of join (INNER, LEFT, RIGHT, etc.) right in the SQL statement. This clarifies the query’s intent and allows for more precise data retrieval.

3. Enhanced Readability for Complex Queries

The difference in readability between the two styles might not be significant for simple queries. However, as queries get more complex and involve multiple tables, the explicit JOIN syntax becomes much easier to read and understand. Each join is clearly specified, and the ON clause for each join clarifies how the tables are related.

4. Future Compatibility

As SQL continues to evolve, there is a trend toward deprecating the old-style comma-separated syntax in favor of the explicit JOIN syntax. By adopting the modern syntax now, you ensure your SQL code remains compatible with future versions of SQL.

What Does the ANSI Standard Say?

The ANSI SQL standard, which guides SQL language usage across different database systems, strongly promotes the use of explicit JOIN syntax.

The standard’s syntax for the JOIN clause is as follows:

table_reference [, table_reference ] ...
table_reference ::= 
table_name [ [ AS ] alias [ ( column_alias [, column_alias ] ... ) ] ] 
[ { LEFT | RIGHT | FULL } [ OUTER ] JOIN table_reference join_condition ]
join_condition ::= ON search_condition

As you can see, the ANSI standard fully supports the different types of joins (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER) using the explicit JOIN syntax with a separate ON clause for join conditions.

The comma-separated syntax, on the other hand, is not included in the ANSI standard for SQL. It’s a legacy syntax from earlier versions of SQL and while it’s still supported by many relational database management systems for backward compatibility, it’s not recommended for use in new SQL code.

So, according to the ANSI SQL standard, the explicit JOIN syntax is the correct and preferred way to join tables in SQL.

Conclusion

While both the old-style comma-separated join and the modern explicit JOIN syntax can achieve the same result, the explicit JOIN syntax offers several advantages regarding readability, clarity, flexibility, and future compatibility. As a result, adopting the explicit JOIN syntax for writing SQL queries is highly recommended. Doing so, you will be writing more maintainable, future-proof code that can handle various data retrieval tasks.

You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)