Where Does the Inner Join Clause Go in a Sql Statement?
Use an SQL INNER JOIN when you need to match rows from two tables. Rows that match remain in the result, those that don't are rejected. The match condition is commonly called the join condition.
When the match conditions involve equality, that is matching exactly the contents of one column to another, the sql inner join is called an equijoin. You'll find that most of the joins you'll use are equijoins. A common situation is where you need to join the primary key of one table to the foreign key of another. You need this when you are denormalizing data.
Table of contents
- SQL INNER JOIN's Basic Structure
- SQL INNER JOIN – Two Tables
- SQL INNER JOIN – Three Tables
- Table Aliases make SQL Joins Easier to Read
- Important Points:
- SQL Non Equi-Joins
- Additional SQL Join Resources
If you're new to SQL, then I would recommend you start with my SQL Joins – The Ultimate Guide. It walks you through fundamentals to give you a solid footing moving forward!
SQL INNER JOIN's Basic Structure
The SQL INNER JOIN is the most common join type you'll use in day to day queries. It the relational database workhorse.
The inner join's main purpose is to combine one or more tables together into a single result. The tables combine by matching columns from one table to the next. Only combinations of rows whose columns match are kept.
Here is the general syntax for SQL INNER JOIN:
SELECT table1.column1, table2.column2, …
FROM table1
INNER JOIN table2 ON table1.matchColumn = table2.matchColumn
Let's now write two inner joins, one for two tables, the other for three. We'll use the following diagram to for our next examples:
SQL INNER JOIN – Two Tables
In this example we're joining the SalesOrderHeader table to SalesOrderDetail. This is a many to one relationship, as there is one SaleOrderHeader row for one or more SalesOrderDetail rows.
SELECT soh.AccountNumber, soh.OrderDate, sod.CarrierTrackingNumber, sod.ProductID, sod.LineTotal FROM sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
SELECT soh.AccountNumber, soh.OrderDate, sod.CarrierTrackingNumber, sod.ProductID, sod.LineTotal FROM sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
Look carefully to see how we join from SalesOrderHeader to SalesOrderDetail. The join condition is SalesOrderID for both tables. Notice we alias the two table to make it easier to distinguish the columns as well as make it easier to read the SQL.
SQL INNER JOIN – Three Tables
One thing you'll notice with this query, it that there the orders show the ProductID, but we have don't know the name. To get this, let's join to the product table. All we do is add another INNER JOIN to our statement.
SELECT soh.AccountNumber, soh.OrderDate, sod.CarrierTrackingNumber, sod.ProductID, p.Name, sod.LineTotal FROM sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
SELECT soh.AccountNumber, soh.OrderDate, sod.CarrierTrackingNumber, sod.ProductID, p.Name, sod.LineTotal FROM sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
To add the third join we included another SQL INNER JOIN clause. This time we join is from SalesOrderDetail to Product. Since we're aiming to get products, the join condition is on ProductID.
Table Aliases make SQL Joins Easier to Read
As you create more complicated SQL statements they can become difficult to read. Much is of this is due to the verbose (wordy) nature of the language. You can cut down on some of this by using another name for your tables in statements. Use table aliases to do so. Think of aliases like nicknames. Luckily is it easy to define Aliases! Just place them after the first reference of the table name. Here is a simple example where we alias the Person table as P:
SELECT P.FirstName, P.LastName FROM Person.Person AS P
In this example we used the letter P. It could really be any number of characters, but I like to keep aliases simple. Where aliases really come in handy is with joins. Let's take last join we did, but this time write it by aliasing Person as P and PersonPhone as PP. Here is the statement
SELECT P.FirstName, P.LastName, PP.PhoneNumber FROM Person.Person AS P INNER JOIN Person.PersonPhone AS PP ON P.BusinessEntityID = PP.BusinessEntityID WHERE P.LastName LIKE 'C%' ORDER BY P.LastName
Regarding naming, I suppose you could alias your tables, A,B,C, but it can become hard to remember the aliases. My system to keep the aliases short, yet easy to remember, is it use the first letter of the table. If the table has two words, like PersonPhone, I'll typically use the first letter of each word.
Watch My Video: How to Make a Join Easier to Read using Table Aliases >>
Important Points:
When working with SQL INNER JOINS keep in mind your match from one table to another may match multiple rows. Meaning, your result may have more rows in the result that you have in either table.
When columns don't match, the query returns zero rows.
You can influence the matching behavior using different types of joins, such as CROSS JOINS, OUTER JOINS.
The WHERE clause can refer to any of the fields from the joined tables. To be clear, it is best practice to prefix the columns with the table name.
The same goes for sorting. Use any field; however, it is common to sort by one or more fields you are selecting.
SQL Non Equi-Joins
A non equi-join is just a fancy way of saying your join condition doesn't have an equals sign in it. You may be scratching your head wondering why you would want to use a non equi-join. I agree, to be honest, it can be hard to think up examples; however, they do exist, and you'll appreciate knowing how to use them when you need to do so. One reason to use a non equi-join is when you need to verify that you have clean data. Consider the AdventureWorks Products table. Check that the product name is unique for each product listed. One way to do this is to self-join the product table on Name.
SELECT P1.ProductID, P1.Name FROM Production.Product AS P1 INNER JOIN Production.Product AS P2 ON P1.Name = P2.Name ORDER BY P1.ProductNumber
This query allows us to compare values for each row that matches the Product Name. If Name is unique, then the only matches you should have are when the ProductID's are equal. So, if we are looking for duplicate Product Name, it follows that we want to find records that match having different ProductID's. This is shown in the diagram below
The SQL to return the duplicate records is:
SELECT P1.ProductID, P1.Name, P1.ProductNumber FROM Production.Product AS P1 INNER JOIN Production.Product AS P2 ON P1.Name = P2.Name AND P1.ProductID <> P2.ProductID ORDER BY P1.ProductNumber
You may wonder what happens first. Does the computer first match the names and then, once that is complete looks for ProductID's that don't match, or does is somehow evaluate both conditions as it builds the result?
The beauty of SQL is that, in theory, the way you write your code shouldn't have an influence on how the database actually retrieves the data. The order of the joins or conditions shouldn't significantly influence the time a query runs. This will all be explored when we talk about the query optimize and how to review query plans.
Additional SQL Join Resources
- SQL Joins – The Ultimate Guide
- What is the Difference between Inner and Outer Joins?
- Rewrite Subquery as a Join
Where Does the Inner Join Clause Go in a Sql Statement?
Source: https://www.essentialsql.com/sql-inner-join/