BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Transact-SQL Joins

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.

Outer Joins

Outer joins use a similar syntax to explicit inner joins but provide different results in some cases. The key difference is that outer joins include all of the rows from one or both tables, even if there are no matching rows defined by the join predicate. Where information is missing, NULL values are substituted in the columns of the returned results. The easiest way to explain the difference is with an example.

Firstly, let's consider an inner join. The query below joins the CustomerComplaints table to the Engineers table so that each complaint can be shown alongside the engineer that was at fault. Although there are three complaints in the database, only one row is returned by the query. This is because the other two rows define complaints that were deemed not to be the engineer's fault. In these cases the EngineerId column in the CustomerComplaints table is set to NULL.

SELECT
    E.EngineerName,
    C.Complaint
FROM
    CustomerComplaints C
INNER JOIN
    Engineers E
ON
    C.EngineerId = E.EngineerId
EngineerNameComplaint
Joey OharaEngineer did not have appropriate parts and was rude.

This may not be the information that we require from the query. If we actually want to list every complaint in the database, showing the engineer's name only where an engineer is associated with the complaint, we must use an outer join. In this case, we will use a left outer join. This indicates that all of the values from the table to the left of the join clause will be returned. If there is no associated row from the table on the right of the join, the columns from that table will be included but will contain NULL values.

To demonstrate, execute the following command, noting that the join clause has been modified to LEFT JOIN:

SELECT
    E.EngineerName,
    C.Complaint
FROM
    CustomerComplaints C
LEFT JOIN
    Engineers E
ON
    C.EngineerId = E.EngineerId

The results from this query contain all three complaints. For the two complaints not associated with an engineer, the EngineerName column's value is NULL.

EngineerNameComplaint
NULLCustomer has received an incorrect charge on their direct debit account.
NULLCustomer does not wish to receive direct marketing information.
Joey OharaEngineer did not have appropriate parts and was rude.

The opposite of a left outer join is a right outer join. As you may imagine, this returns all of the rows from the table on the right of the join clause with null values for columns from the table to the left of the clause when no matching row exists. If you alter the previous query to use a right join, all twenty-five engineers will be returned. One of these engineers, Joey Ohara, will have a complaint listed, whilst all of the others will have NULL in the Complaint column.

SELECT
    E.EngineerName,
    C.Complaint
FROM
    CustomerComplaints C
RIGHT JOIN
    Engineers E
ON
    C.EngineerId = E.EngineerId

The third style of outer join is the full outer join. This type of join ensures that every row from both tables is included in the final results. If any row in either table does not have a matching partner, the row is populated with NULL values for the missing data. Try modifying the query to use a full join as shown below. This time you will see twenty-seven returned rows. One will be a complaint with a matching engineer, two will be complaints without engineers and twenty-four will be for engineers with no complaints made against them.

SELECT
    E.EngineerName,
    C.Complaint
FROM
    CustomerComplaints C
FULL JOIN
    Engineers E
ON
    C.EngineerId = E.EngineerId

Cross Joins

Cross joins are the simplest form of join and possibly the least used. A cross join does not define a join predicate. This means that the results contain every possible combination from the two tables. This is the Cartesian product of the table so this type of join is often called a Cartesian join.

If we modify the outer join query to provide a cross join, all combinations of data are returned. As there are twenty-five rows in the Engineers table and three rows in the CustomerComplaints table, the resultant list contains seventy-five rows.

SELECT
    E.EngineerName,
    C.Complaint
FROM
    CustomerComplaints C
CROSS JOIN
    Engineers E

NB: Cross joins should be used with care, particularly with large tables as the number of results can grow very quickly.

Implicit Cross Joins

As with inner joins, cross joins have an implicit syntax variation. This is the same syntax as for inner joins but with no join predicate in the WHERE clause:

SELECT E.EngineerName, C.Complaint FROM CustomerComplaints C, Engineers E
28 December 2008