SQL: Intro to Joins

More often than not, the information you need does not all reside in one table. To query information from more than one table, use the Join command.

In our example, we are going to be using AdventureWorks2012. If you want to follow along, but do not have SQL Server or AdventureWorks2012 installed, click in the following links:

We are going to be using the following Tables for our Join

Notice they both have a matching field – BusinessEntityID – This field will be important when creating a join.

sqlJoin7

We can call up both tables individually through separate SQL Queries

sqljoin9

However, to combine the two tables into a single result, you will need a Join.

Inner Join

SQL uses Inner Joins by default. In this lesson, I am only going to focus on inner joins. Inner joins work by focusing on columns with matching information. In the example below, the columns with matching information are the Name columns.

The combined result only contains rows who have a match in both source tables. Notice that Sally and Sarah do not appear in the result table.

sqljoin10.jpg

The syntax is as follows:

Select *
from Table A join Table B
on TableA.Match = TableB.Match

sqljoin11.jpg

In our example, the matching column is BusinessEntityID in both tables.

To clean the code up a little bit, we can assign aliases to our tables using the “as” keyword. We are using E and S as our aliases. We can now use those aliases in our “on” clause.

sqlJoin12

And, just as in a regular select statement, we can choose which columns we want. You do however, have to identify which table the columns you are requesting are from. Notice how I did this using the E and S aliases.

sqljoin13.jpg

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s