Inner Joins in SQL

In this post we go over one of the most commonly used SQL joins called an ‘inner join’.

Learning Objectives:

• Define and describe an inner join.
• Explain when and how to use an inner join.
• Pre-qualify column names to make your SQL code that much cleaner and more efficient.

An inner join is used to select records that have matching values in two tables. This means that a join will return only records that are matching in both tables.

Inner Join Example

SELECT suppliers.companyname,
productname, 
unitprice
FROM suppliers INNER JOIN products
ON suppliers.supplierid = 
products.supplierid

In this query, we are still selecting the column names we want from both tables. But, we also have to tell the query how we are going to join the tables and where we will join the data.

In this example, we use are saying we want to join ‘suppliers’ and ‘products’ on the column name ‘supplierid’ in the ‘suppliers’ table and the ‘supplierid’ in the ‘products’ table.

We also used ‘suppliers.companyname’ from in the initial SELECT statement. This lets the query know that we want to get the ‘companyname’ from the ‘suppliers’ table since in this instance ‘companyname’ is a column available in both tables.

Inner Join Syntax

• Join type is specified (INNER JOIN)
• Join condition is in the FROM clause and uses the ON clause.
• Join more tables together affects overall database performance.
• You can join multiple tables, with no limit.
• List all the tables then define the conditions.

Inner Join with Multiple Tables.

SELECT o.OrderID,
c.CompanyName,
e.LastName
FROM ((Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID)
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID);

This might look a little tricky but we are making the query shorter by using this. The letters that precede the column names refer to tables and we have defined the actual table names in the FROM statement and INNER JOIN statement. This makes it easier to type and understand.

We are saying we want to INNER JOIN ‘Orders’ (now defined as o) and ‘Customers’ (now defined as c) ON ‘o.CustomerID’ and ‘c.CustomerID’.

Then define a second INNER JOIN defined the ‘Employees’ table as e. This statement joins on ‘o.EmployeeID’ and ‘e.EmployeeID’.

You can see that we should be specific about column names that are the same in multiple tables so that we can correctly join data using INNER JOIN.

Best Practices with Joins.

• Make sure you are pre-qualifying names.
• Do not make unnecessary joins.
• Think about the type of join you are making.
• How are you connecting records?

I would also love to connect on Twitter, LinkedIn or on my Website. I am not sure what value I could provide you with but if you reach out, I would be more than happy to have a conversation or talk about what I have written here.