Sign in
Imagine you work at a manufacturing company and you are responsible for analyzing the performance of various products. Your database has two tables: ‘product information’ (products) and ‘sales data’ (sales). Your task is to combine data from both tables to gain insights and improve decision-making. SQL Joins can be incredibly helpful in this situation, as they allow you to combine data from multiple tables based on related columns.
Let’s go through the different types of Joins and see how they can help answer real-world data analytics questions.
Inner Join
Question: Which products have been sold, and what are their total sales quantities?
Example query:
The INNER JOIN clause combines the ‘products’ and ‘sales’ tables based on the matching ‘product_id’ values. The resulting data set only includes rows with a match in both tables, allowing you to calculate the total sales quantities for the products sold.
Left Join (Left Outer Join)
Question: What are their total sales quantities for all the products, whether sold or not?
Example query:
The LEFT JOIN clause combines the ‘products’ and ‘sales’ tables based on the matching ‘product_id’ values but also keeps all rows from the ‘products’ table, even if there is no match in the ‘sales’ table. The COALESCE function replaces any NULL values (for unsold products) with 0, giving you the total sales quantities for all products.
Right Join (Right Outer Join)
Question: Which sales records are associated with products, and which products are missing from the products table?
Example query:
The RIGHT JOIN clause combines the ‘products’ and ‘sales’ tables based on the matching ‘product_id’ values but also keeps all rows from the ‘sales’ table, even if there is no match in the ‘products’ table. This way, you can identify sales records associated with products and any sales records with missing product information.
Full Join (Full Outer Join)
Question: What is the complete list of products and sales, including products that have not been sold and sales records with missing product information?
Example query:
The FULL JOIN clause combines the ‘products’ and ‘sales’ tables based on the matching ‘product_id’ values but also keeps all rows from both tables, even if there is no match. This gives you a complete list of products and sales, including unsold products and sales records with missing product information.
Using these join types, you can now effectively analyze your manufacturing data and make well-informed decisions to improve your company’s performance.