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.

Joins

In the previous two articles we have examined the process of normalising a database and creating foreign key relationships between tables that are logically linked. When you are working with a normalised relational database, it is essential that you can perform queries that gather data from several linked tables but return the matching data in a single set of results. This is achieved with the use of joins.

A join is a clause within a SELECT statement that specifies that the results will be obtained from two tables. It usually includes a join predicate, which is a conditional statement that determines exactly which rows from each of the tables will be joined by the query. Usually the join will be based upon a foreign key relationship and will only return combined results from the two tables when the key values in both tables match. However, it is possible to join tables based upon non-key values or even perform a cross join that has no predicate and returns all possible combinations of values from the two tables.

Usually normalisation results in a database with many related tables. You may therefore want to join more than two tables for a single set of results. In this case, you can include multiple joins in a SELECT statement. The first join combines the first two tables. The second join combines the results of the first join with a third table, and so on.

JoBS Database

The examples in this article use the JoBS tutorial database. If you do not have a copy of the JoBS database available, download the script using the link at the top of this page. You can execute the script to create and populate a new database.