“A Brief Snapshot of SQL Joins History ( SQL 92 and SQL 89 syntax)”

Coming from a C# background I do get language designers when they want alternative syntaxes in the sake of verbosity.

SQL do have a lot of alternative syntaxes like the SQL - 89 and SQL -92 syntaxes. It’s not a language design choice but it’s that way to support legacy.

It is made for a backward compatibility. Think of it like the CTS(Common Type System) on C# if you’re familiar with it. On C# we can write int and System.Int32 even though there is a bigger purpose for maintaining the CTS on c# version but i hope you do get the idea.

Let’s start with the JOINS.

It’s all Maths, in order to visualize joins in SQL you need a little bit of knowledge on what is a Cartesian Product. The cartesian product is the mathematical foundation of joins.

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 an element of A and b is an element of B. In layman terms every possible combination of rows from A and B.

Cross Joins - Implements only one logical query processing phase , a cartesian product.

Each row from Orders will be repeated once for every employee row, even though you’re not displaying any employee columns. (It’s A x B)

The SQL -92 syntax

The SQL 89 syntax

This is also a cross join , take note of the ‘,’ that makes this a cross join.

You can even try to cross join it with the table itself.

Inner Joins

Applies two logical query processing phases - it applies cartesian product between the two input tables like in a cross join, and then it filters rows based on a predicate you specify. Here there is a new variable, a predicate.

“The simplified way to think of this inner join is as matching each employee row with all order rows that have the same employee ID as in the employee row. “

The formal explanation would be based on relational algebra. First, the join performs a cartesian product between the two tables.

The SYNTAX 92

Here it’s (employees rows x order rows) then join filter rows based on the predicate which is our new variable. (EMP.empId = ORD.empId)

The Syntax 89

Similar to the cross joins, you specify a comma between the table names and then specify the join condition in the where query. There is no performance difference on these syntaxes, but here the syntax 92 is much more safe.

If by chance we mistakenly wrote a query with the intent of INNER JOIN and we forgot to write the predicate, our query might look like this.

Take a look of the error, where it’s saying an incorrect syntax.

Now let’s try to write the same query with the SQL 89 syntax with the intent of INNER JOIN, if you have again forgot to write the predicate, it will not yield a syntax error. Instead, a valid cross join will be formed.

Composite Joins

A composite join is a type of join where multiple attributes from each table are used to establish the relationship between them. Instead of matching a single column, you match two or more columns to ensure the join is precise.

Auditing Scenario

Suppose you need to track changes in the qty column of the OrderDetails table. You want a query that returns:

• The current value from the OrderDetails table.

• The previous value and new value recorded in the Audit table for every change.

Non - equi Joins

When a join condition involves only an equality operator .<, >, <=, >=, !=, BETWEEN, etc.

Outer Joins ( Left- Join)

We’re introduced in SQL - 92 and unlike any joins, this have only one standard syntax

Outer joins apply the two logical processing phases that inner joins apply ( Cartesian product and the ON filter) plus a third phase called adding outer rows that is unique to this type of join.

The OUTER is optional

Every blog you write should be always an evolving blog, not being arrogant on this world will do you much better. This post is just a mere snapshot of a history on SQL. Now I hope i helped someone to not get confused today.