SQL 2005+

Transact-SQL Joins

by Richard Carr, published at http://www.blackwasp.co.uk/SQLJoins.aspx

The nineteenth part of the SQL Server Programming Fundamentals tutorial returns to the topic of querying. In this article, we examine joins using Transact-SQL (T-SQL). Joins allow data from several tables to be combined in a single set of results.

Implicit Inner Joins

A second syntax can be used for creating inner joins without providing a join clause. This syntax is known as an implicit inner join. In this case, the names of the tables to be joined are provided in a comma-delimited list. The join predicate becomes part of the WHERE clause.

SELECT
    Jobs.EngineerId,
    JobId,
    VisitDate,
    EngineerName
FROM
    Jobs, Engineers
WHERE
    Jobs.EngineerId = Engineers.EngineerId

Generally speaking, the syntax that you use for inner joins is a matter of personal preference or will be proscribed by your organisation's coding standards. Many people prefer the explicit syntax as the join predicates are separated from those in the WHERE clause and are kept closer to the names of the tables that they are acting upon. It is also easier to change the type of join with the explicit syntax. It is useful to understand both variations, as you are likely to encounter each syntax in real-world scenarios.

Using Table Aliases

Providing the full name of the table as a prefix to column names can lead to long query statements. This is especially true if, to make the query more easily understood, you prefer to prefix every column name rather than just those that are ambiguous. In such circumstances, it is useful to replace the table names with table aliases.

A table alias provides a short code, usually of one or two letters, that can be used in place of a table name. Each alias is defined in the query after the full table name. In the next example, the "J" alias represents the Jobs table and the Engineers table has the alias "E". This makes the query much more readable.

SELECT
    J.EngineerId,
    J.JobId,
    J.VisitDate,
    E.EngineerName
FROM
    Jobs J
INNER JOIN
    Engineers E
ON
    J.EngineerId = E.EngineerId

Self-Referencing Joins

Table aliases are always required when creating self-referencing joins, where the two tables that are being joined are actually the same table. In the following example, the Jobs table is being joined to itself so that the initial job and follow up job can be combined in a single row in the results. The initial job's table has an alias of "J", whilst the follow up job uses the "JF" alias.

SELECT
    J.JobId,
    J.StandardJobId,
    J.EngineerId,
    JF.JobId AS FollowUpJobId,
    JF.StandardJobId AS FollowUpStandardJobId,
    JF.EngineerId AS FollowUpEngineerId
FROM
    Jobs J
INNER JOIN
    Jobs JF
ON
    J.FollowUpJobId = JF.JobId

Joining Multiple Tables

When you wish to join more than two tables, you can simply add further INNER JOIN and ON clauses to the query. For example, in the JoBS database there is a many-to-many link between Engineers and their Skills via a junction table named "EngineerSkills". We can join the three tables to generate a list that contains all engineers that have skills with one row for every engineer and skill combination. The following query returns these results in order of skill name. Note that some engineers appear more than once in the list because they have multiple skills.

SELECT
    E.EngineerName,
    E.HourlyRate,
    E.OvertimeRate,
    S.SkillName
FROM
    Engineers E
INNER JOIN
    EngineerSkills ES
ON
    E.EngineerId = ES.EngineerId
INNER JOIN
    Skills S
ON
    ES.SkillCode = S.SkillCode
ORDER BY
    S.SkillName