Sign-up now and get instant access
Leave a comment
Customized training to master new skills and grow your business.
Beginner to advanced classes taught by Microsoft MVPs and Authors.
In-depth boot camps take you from a novice to mastery in less than a week.
Season Learning Pass
Get access to our very best training offerings for successful up-skilling.
Stream Pro Plus
Combine On-Demand Learning platform with face-to-face Virtual Mentoring.
Quick references for when you need a little guidance.
Summaries developed in conjunction with our Learn with the Nerds sessions.
Digital goodies - code samples, student files, and other must have files.
Stay up-to-date on all things Power BI, Power Apps, Microsoft 365 and Azure.
Earn money by driving sales through the Pragmatic Works' Training Affiliate Program.
It's time to address your client's training needs.
Learn how to get into IT with free training and mentorship.
Discover the faces behind our success: Meet our dedicated team
How can we help? Connect with Our Team Today!
Find all the information you’re looking for. We’re happy to help.
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.
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
2. Left Outer Join
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.
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.
Austin starts by demonstrating an inner join:
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.
Now, Austin shows how a left outer join works:
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.
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.
ABOUT THE AUTHOR
Austin is a Jacksonville native who graduated from The Baptist College of Florida in 2012. He previously worked as a manager in the retail service industry. He enjoys spending time with his wife and two kids. His primary focus at Pragmatic Works is on Azure Synapse Analytics and teaching the best practices for data integration, enterprise data warehousing, and big data analytics. He also enjoys helping customers learn the ins and outs of Power BI and showing people new ways to grow their business with the Power Platform.
Join other Azure, Power Platform and SQL Server pros by subscribing to our blog.