AzureFeeds

The most common form of join between two tables is what’s called an inner join.

In the example shown above, I’ve selected some columns from different tables. I’ve got description and product ID from the product table and I’ve got the product group name from the product group. What I wanted was a list of products but I want to know what group they’re part of.  So to do that I had to use the products table, but I also had to join to the product groups table so I could get the name of the product group.

Notice that the ON clause says ProductGroupID in the Products table (i.e. a foreign key in that table) is the same as the ProductGroupID in the ProductGroups table (where it is the primary key).

Abbreviation

The clause INNER JOIN can be abbreviated to just JOIN.

If you only ever work with SQL Server, that’s probably fine. I prefer the clarity of saying INNER JOIN. Some other SQL engines require it and will not work with the abbreviation. But that might well not matter to you.

Aliases

Also notice how useful the aliases are here, the p and the pg. As soon as you start having multiple tables, it starts to become quite useful to use aliases.

Now, notice also that the product name was an alias. I mentioned in earlier topics about the logical order of the execution of queries. I was able to use ProductName for ordering, even though it’s an alias, in the ORDER BY clause, because the ORDER BY is processed logically after the SELECT. I could just as easily have ordered by p.[Description] to get the same result.

In the end, what the inner join achieves is returning rows that match between the two tables, based on the ProductGroupID column.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #104 Using an INNER JOIN appeared first on The Bit Bucket.