Learning SQL: Selecting from multiple tables using INNER JOIN

2007-04-20T06:39:00-04:00

Many years ago, when I started learning SQL, I used Microsoft’s documentation (because I was learning on SQL Server 6.5) and much of my early knowledge of SQL was based on their way of doing things.

In the Microsoft documentation, nearly all of the examples used the short-hand notation for joins:

SELECT *
    FROM TableOne t1, TableTwo t2
    WHERE t1.SomeKey = t2.Id
        AND t1.SomeValue = 'search'

I never really liked this, but I couldn’t quite explain why.

It was only when I came across a ‘standards compliant’ ANSI SQL tutorial that I realised that this was, in fact, completely the wrong way of doing it.

Sadly, I still see lots of code being written this way. I recommend that all developers who are writing SQL code should force themselves to use the standards compliant syntax.

The correct way is to use the key words INNER JOIN:

SELECT *
    FROM TableOne t1
    INNER JOIN TableTwo t2
        ON t1.SomeKey = t2.Id
    WHERE t1.SomeValue = 'search'

What you are saying is “join each row of TableTwo to the results in TableOne where the join criteria match.”

It’s more verbose, but it is also much clearer. Clarity is a key feature of good programming style.

The above example is only simple, but as your queries get more complex the benefits grow exponentially. Using the short-hand syntax, our WHERE clause is doing two jobs – it is joining our tables and filtering the results. By doing two jobs we are making it harder to read and harder to understand. With the INNER JOIN, our joins are explicity declared with the table that is being joined. The WHERE clause is filtering the records after all of the joins have completed.

It is also worth noting that you must use the standard language for LEFT OUTER JOINs, a very powerful type of join.

Arctus Blog: Learning SQL: Selecting from multiple tables using INNER JOIN

Learning SQL: Selecting from multiple tables using INNER JOIN