Step by Step Join Syntax in T-SQL FROM command

Joins allow you to select records from more than one table where you specify records to include based on field overlaps. For instance, an INNER JOIN would return only records that have values in both tables. So if you have  a table with a ResellerKey field as primary key and another table has ResellerKey as a foreign key as these are related you can query records that are related. As always in your new query start with USE databasename and follow with the SELECT FROM commands.

USE AdventureWorksDW2008R2 

SELECT

FROM

Next to FROM you would first list the tables you want. Next to each you give them an Alias or Alternate name, preferrably shorter and easier to use, using the AS statement. So let's say you want the FactResellerSales table and the DimReseller table and you want to give them an Alias of FRS and R respectively.

USE AdventureWorksDW2008R2 

SELECT

FROM
	FactResellerSales as FRS
	DimReseller as R

After this you add ON equating the the overlapping field. This is the field you find in both tables. In this case ResellerKey. You add the Alias for each table and a period to this field to show it equates in both tables.

USE AdventureWorksDW2008R2 

SELECT

FROM
	FactResellerSales as FRS
	DimReseller as R
	on FRS.ResellerKey = R.ResellerKey

Now you want to add your Join. Types of Join and what they mean are outside the scope of this blog. If you want to use an INNER JOIN you would type it before the DimReseller as R.

USE AdventureWorksDW2008R2 

SELECT

FROM
	FactResellerSales as FRS
	INNER JOIN 
	DimReseller as R
	on FRS.ResellerKey = R.ResellerKey

Now you can choose which columns you want to SELECT. In this case we want SalesAmount from the FactResellerSales table and ResellerName from the DimReseller table. We include the Alias of their table and a period followed by the column name in our SELECT statement. Remember to put a comma at the end of each column that has another declared right after it. You don't need the comma after the last column you are selecting in your SELECT statement.

USE AdventureWorksDW2008R2 

SELECT
	FRS.SalesAmount, 
	R.ResellerName
	
FROM
	FactResellerSales as FRS
	INNER JOIN 
	DimReseller as R
	on FRS.ResellerKey = R.ResellerKey

At this point you should be able to Execute the query. You will see a column for SalesAmount and one for ResellerName in your results. Happy JOINing to you!