SQL Joins - Inner vs. Outer

In this instructional video, Austin Libal, a trainer at Pragmatic Works, walks viewers through one of the foundational concepts in SQL: joining tables using INNER JOIN and LEFT OUTER JOIN. These two types of joins are among the most commonly used and are essential for anyone working with relational databases.

Why Joins Matter

Joins allow users to combine data from multiple tables to generate meaningful insights. For example, a Product table might contain all available products, while a Sales table lists individual transactions. To analyze product performance, these tables must be joined meaningfully.

The INNER JOIN

Austin begins by explaining the INNER JOIN, which returns only the matching records from both tables involved in the join. Using a classic Venn diagram for illustration, he highlights that:

• Only records present in both tables are included in the result set.
• For example, only products that appear in both the Production.Product and Sales.SalesOrderDetail tables will be shown.
• This type of join is best for finding intersecting data — such as all sold products.

The LEFT OUTER JOIN

Next, Austin explains the LEFT OUTER JOIN, which includes all records from the left (first) table and any matching records from the right (second) table. Key takeaways include:

• All records from the left table are included, even if there are no matches in the right table.
• This is useful for identifying entries in the left table that lack corresponding entries in the right one — for example, products that haven't been sold.
• Null values will appear in columns from the right table when there’s no match.

Demonstration with AdventureWorks Database

To bring theory into practice, Austin demonstrates SQL queries using the AdventureWorks database. Here's a breakdown of the exercise:

1. He starts by running SELECT * FROM Production.Product and finds 504 total products.
2. Next, he inspects the Sales.SalesOrderDetail table, which contains 121,000+ sales entries.
3. By running SELECT DISTINCT ProductID FROM Sales.SalesOrderDetail, he discovers that only 266 unique products have been sold.
4. This leaves approximately 238 products that have not been sold — highlighting the need for a LEFT OUTER JOIN.

Writing the INNER JOIN

In the next step, Austin writes an INNER JOIN using aliases to simplify syntax:

SELECT p.ProductID
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS sod
  ON p.ProductID = sod.ProductID

He points out the importance of qualifying column names (like p.ProductID) to avoid ambiguity, especially when both tables share column names.

Writing the LEFT OUTER JOIN

To retrieve all products, regardless of whether they've been sold, Austin modifies the previous query:

SELECT p.ProductID
FROM Production.Product AS p
LEFT JOIN Sales.SalesOrderDetail AS sod
  ON p.ProductID = sod.ProductID
ORDER BY p.ProductID

He explains that this LEFT JOIN pulls all products from the product table and includes sales data when available. Products without sales appear with NULL in the sales columns, effectively showing what hasn’t been sold.

Use Cases for INNER vs. LEFT OUTER JOIN

Austin wraps up by emphasizing when to use each type of join:

• Use INNER JOIN when you're interested only in records that have a match in both tables (e.g., products that were sold).
• Use LEFT OUTER JOIN when you want to include all records from the first table, regardless of whether there's a match in the second (e.g., identifying unsold products).

Learn More with Pragmatic Works

This session is part of Pragmatic Works' training series on SQL. For those eager to deepen their SQL expertise, Austin recommends exploring the company’s on-demand learning library or attending a live SQL boot camp. These resources offer structured lessons designed to elevate learners from beginner to advanced levels rapidly.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on  SQL and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.