T-SQL 101: 108 Joining more than two tables

Of course, you might also need to join more than two tables. The example above shows how to do that.

I started with the Orders table, then joined to the OrderLines table, and finally, I’ve also joined to Products.

It’s another example of why I prefer the modern join syntax. Instead of just listing three tables and a bunch of WHERE clause predicates, for each table involved, I specify how it’s joined.

Column Order in ON

The order that the columns are shown in the ON clause doesn’t matter but I have a preference to put the table I just joined first, and then put what it’s joined to.

The ON clause can refer to anything in any of the previous tables. So I’m joining Products, in this case it joins to OrderLines, but it could also have joined to any other table higher up in the list if that made sense. Each table can match to any prior value that’s already part of the list of tables (or table expressions) that are being listed here.

There is also one weird option where you can put all the ON clauses at the end. Don’t do that. Make things obvious.

Indentation

You might also want to consider indentation.

Some people like to put the ON clause on the end of the line after the table name, instead of on new lines. I’m not a fan of that, as you can have multiple predicates in the ON clause, connected with AND and OR as you’d expect.

Other people like to indent just the ON clause predicates. I don’t mind that and I think it can look pretty good. I haven’t done so here for brevity.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-04