Inner vs. Outer in SQL

In this blog post, we'll dive into the world of SQL joins, specifically focusing on the differences between inner joins and left outer joins. Austin Libal, a trainer at Pragmatic Works, breaks down these essential concepts in SQL, making it easier for beginners to understand and apply them effectively.

SQL, or Structured Query Language, is a powerful tool for working with databases. One common task in SQL is joining tables to retrieve the desired results. Joining is the process of combining data from two or more tables based on a related column. There are various types of joins, but for now, we'll concentrate on inner joins and left outer joins.

Understanding SQL Joins

To grasp the concept of joins, we can use a Venn diagram as a visual aid. Imagine two circles representing two different tables: one for products and one for sales. These circles intersect in different ways based on the type of join you use.

1. Inner Join

• An inner join returns records that match in both tables. In our example, it retrieves products that have been sold.
• Products that appear in the product table but have not been sold won't be included in the result.
• Inner joins are like finding the common ground between two tables.

2. Left Outer Join

• A left outer join returns all records from the left table (products) and matching records from the right table (sales).
• This means you'll see all products, whether they've been sold or not.
• It's useful when you want to see the full list of products and their sales data, if available.

Practical Example

To illustrate these concepts, Austin uses the Adventure Works database:

1. Products Table: This table contains information about different products that the company can sell. There are 504 products listed.

2. Sales Order Detail Table: This table contains sales data, with a relationship to the Products Table through the Product ID column.

• • There are 121,000 sales records, with some products appearing multiple times.
    
    

To understand how many products have been sold and how many haven't, Austin uses the DISTINCT keyword to count unique occurrences of the Product ID in the Sales Order Detail table.

• 266 distinct product IDs in sales data.
• This means approximately 250 products have not been sold (504 - 266).

Inner Join in Action

Austin starts by demonstrating an inner join:

• Selects the Product ID from the Products Table (as the left side of the join).
• Uses the INNER JOIN clause to connect the Sales Order Detail Table (as the right side).
• Specifies how the two tables are related using the ON keyword.
  
  

Executing the query returns 121,700 rows, which are identical to the sales data alone. This is because an inner join only shows the common records between the two tables. It provides context and additional information about the sales data.

Left Outer Join in Action

Now, Austin shows how a left outer join works:

• Uses a LEFT JOIN clause to combine the two tables.
• Orders the results by Product ID for clarity.
  
  

The result is a combination of all products from the Products Table and their corresponding sales data. Products that haven't been sold are also included. This is the key difference between a left outer join and an inner join - it shows all records from the left table.

Conclusion

Understanding the difference between inner and left outer joins is essential for anyone working with SQL. Inner joins provide specific, matching data, while left outer joins give a comprehensive view, including unmatched records. Depending on your analysis goals, you can choose the appropriate join type to extract the desired insights from your data.

In the SQL world, there are many other types of joins, each serving a unique purpose. If you're new to SQL or want to explore these join types further, consider checking out Pragmatic Works' on-demand library or participating in their live boot camps. With the right training, you can become proficient in creating SQL statements and handling complex data analysis tasks. 

We hope this blog post has clarified the fundamentals of inner and left outer joins in SQL and set you on the path to becoming a proficient SQL user. Lastly, don't forget to check out the Pragmatic Works' on-demand learning platform, which offers a wide range of courses, including those related to Microsoft products like Power BI, Power Automate, and Azure.