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

Basic Inner Joins – Basic Skill #4

This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #4 – Joins.

T-SQL Tuesday #23

This post is also serving as my T-SQL Tuesday post for the month. This month the party is brought to you by Stuart Ainsworth. If you want to know more about what T-SQL Tuesday is about, read Stuart’s post as well as Adam Machanic’s original post and then join in next month.

An inner join is essentially an intersection of two sets. If you go back to grade school, and think about sets, you can have two items like this:

set1

If you look at these two sets, you see that there are various elements in each set. If we were to show the intersection, it would be this set: (B). This is shown below (excuse my horrible artwork):

set2

In SQL, we deal with tables, but we can model this as follows:

CREATE TABLE SET1
( mychar varchar(1) ) GO CREATE TABLE SET2
( mychar varchar(1) ) GO INSERT SET1 SELECT 'A' INSERT SET1 SELECT 'B' GO INSERT SET2 VALUES ('B'), ('C'), ('D') GO SELECT a.mychar
  , b.mychar
 FROM SET1 a
   INNER JOIN set2 b
     ON a.mychar = b.mychar
mychar mychar
------ ------
B      B

The results are the matching values in each table. In this case each table is a single column, modeling the images above where there is a single letter in each item of the set. The matching columns are the join columns, and in database work, these would be the data items that we are storing in both tables.

However to expand this, in a database table, we usually have multiple items, so each letter could be a series of data elements, or could have a series of other fields attached to it. Suppose I change these “sets” a little:

DROP TABLE set1
DROP TABLE dbo.SET2
go CREATE TABLE SET1
( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO CREATE TABLE SET2
( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO INSERT SET1 SELECT 'A', 'Bob', '1/1/2011' INSERT SET1 SELECT 'B', 'Bill', '2/1/2011' GO INSERT SET2 VALUES ('B', 'Steve', '1/2/2011'), ('C', 'Andy', '3/1/2011'), ('D', 'Brian', '3/3/2011') GO SELECT a.mychar
  , b.mychar
 FROM SET1 a
   INNER JOIN set2 b
     ON a.mychar = b.mychar

I still only have one matching row, but there are other data points. If I alter my diagram, they look like this:

set3

The matching rows, in this case the rows with a “B” in them, have different data, which seems to be an issue. However suppose set 1 was a list of customers along with their first order date and set 2 was a list of salespeople and the dates they started. Then the join might be on sales, with the letter (A, B, C, D) representing the order.

That’s the basic of a join. It gets complicated as you look to join three, four, or more tables, but this is the basic idea of an inner join in SQL.


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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.