SQL Server Power Search - Get More Relevant Results

SQL JOIN

The SQL JOIN clause enables rows from 2 or more tables to be combined in a single query.

 

Consider the following data model:

sql join sample data model

The previous tutorials have mainly used single table queries for simplicity, but in reality you are likely to need to retrieve columns from more than table.

Using this data model, let's say we want the first name, last name and order dates for all orders by a particular customer:


There are 2 things to mention here before continuing:
  1. I have used aliases in this query and will do so for all queries in this article. It saves typing and I think it makes the query easier to read.
  2. "Order" is a reserved word in SQL, so to avoid errors I have enclosed the Order table in square brackets. This tells SQL Server to treat it as an object.
Assuming the customer with Id 123 (John Smith) has placed 2 orders so far, you would get a similar output to this:

FirstNameLastNameOrderDate
JohnSmith10-Jan-2008
JohnSmith17-Apr-2008

Compare the structure of the JOIN clause with the data model. You can see that there is a column in the Order table called CustomerId; this is what links the Order and Customer tables.

This relationship has been represented by the 2 parts of the JOIN clause. FROM Customer AS c JOIN [Order] AS o specifies the tables to be joined. ON o.CustomerId = c.Id indicates the columns to use for the join (represented by the line in the diagram).

This join is known as an inner join, because it will only return data where there is a matching row in each table. The JOIN keyword could have been replaced by INNER JOIN.

Another type of join is the outer join. This will match rows even when one table has no corresponding row in the other. The 2 types of outer join are the left join and the right join. Use LEFT OUTER JOIN (or LEFT JOIN; the OUTER keyword is optional) when there may not be a matching row in the second table. Use RIGHT OUTER JOIN when all matching rows from the second table are needed, but there may be no corresponding row in the first table.

For example, suppose there are customers in the Customer table who have never placed an order. We want to modify the first query so that a row is still displayed for these customers:

FirstNameLastNameOrderDate
JohnSmith10-Jan-2008
JohnSmith17-Apr-2008
FredBloggsNULL

Several JOIN clauses can be strung together to allow a chain of tables to be joined. To get a list of distinct items ordered by each customer you could write this:


In additional to the standard SQL JOIN there is also a CROSS JOIN, and more advanced use of inner and outer joins such as the self join. All will be covered in future tutorials. Please subscribe to my RSS feed for future updates.

The next article describes the SQL UNION operator.