This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.
Databases are built to store data. That’s the primary purpose, and in SQL Server, we store data in a relational form. That means that often we have data spread across multiple tables. Why we do this is a discussion for another day, but suffice it to say that we often have structures like this:
Part of the person.contact table in AdventureWorks above and the HumanResource.Employee table below.
One typical join task might be to get an employee’s name, or a list of employees and their names. Here we have a birthday in the Employee table, but we don’t have a name. That’s in the Person.Contact table. Essentially we want to match these up using basic, elementary school set theory.
In the diagram above, you can think of each letter as a row in a table. As an example, let’s assume that B in the orange circle represents the row in the employee table with a ContactID value of 4. The B in the pink circle would represent the row in the Contact table with a ContactID value of 4 as well.
When we join these to get the Employee name and birth date, we get:
I used a join in my query to get that:
SELECT
c.firstname
, c.LastName
, e.BirthDate
FROM person.contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
WHERE c.ContactID = 4
In this query I’ve included two tables in the FROM clause with the INNER JOIN key phrase between them, which specifies I only choose the matching rows. The match is made in the ON clause.
I’ve also qualified this to only apply to the row with a ContactID of 4 in the WHERE clause.
There’s a lot more you can do with joins, and you can include more than two tables, such as this query:
SELECT
c.firstname
, c.LastName
, e.BirthDate
, pa.AddressLine1
, pa.AddressLine2
FROM person.contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeAddress ea
ON e.EmployeeID = ea.EmployeeID
INNER JOIN person.Address pa
ON ea.AddressID = pa.AddressID
WHERE c.ContactID = 4
I would recommend that you practice working with basic joins, based on the information that you commonly see queried in your application. Sooner or later someone will ask you for some data that isn’t available in the application and you will want to write a query to extract it for them.
