Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

The Basics of Joins – Skill #4

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:

personcontact

Part of the person.contact table in AdventureWorks above and the HumanResource.Employee table below.

employee

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.

settheory

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:

join2

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.


Filed under: Blog Tagged: syndicated, T-SQL, TopTenSkills

Comments

Posted by Jason Brimhall on 26 October 2011

Nice article on join basics.

Leave a Comment

Please register or log in to leave a comment.