SQL Join Techniques: Filtering Data with WHERE vs. ON Clause
"In this video, we'll explore the difference between filtering data in SQL using the WHERE clause versus the ON clause during joins. We'll start with a simple product and shelf life dataset, create the tables, and insert sample data. Then, we'll walk through several steps to demonstrate how each filtering method affects the results. Perfect for SQL learners who want to deepen their understanding of joins and filtering in SQL. Don't miss out—this is a critical skill for efficient data querying!"
--How can you filter milk products based on their shelf life using SQL joins,
--and what is the difference between filtering in the WHERE clause versus the ON clause?"
CREATE TABLE Milk_Products_Name (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(100)
);
INSERT INTO Milk_Products_Name (Product_ID, Product_Name) VALUES
(1, 'Whole Milk'),
(2, 'Skim Milk'),
(3, 'Almond Milk'),
(4, 'Soy Milk'),
(5, 'Coconut Milk');
CREATE TABLE Milk_Products_SL (
Product_ID INT PRIMARY KEY,
[Shelf_Life (in days)] INT
);
INSERT INTO Milk_Products_SL (Product_ID, [Shelf_Life (in days)]) VALUES
(1, 7), -- Whole Milk: 7 days
(2, 10), -- Skim Milk: 10 days
(3, 5), -- Almond Milk: 5 days
(4, 3), -- Soy Milk: 3 days
(5, 8); -- Coconut Milk: 8 days
--Step 1
Select * from Milk_Products_Name
--Step 2
Select * from Milk_Products_SL
--Step 3
Select MPN.Product_ID, MPN.Product_Name, MPSL.[Shelf_Life (in days)]
From Milk_Products_Name MPN LEFT JOIN Milk_Products_SL MPSL
ON MPN.Product_ID = MPSL.Product_ID
--Step 4
Select MPN.Product_ID, MPN.Product_Name, MPSL.[Shelf_Life (in days)]
From Milk_Products_Name MPN LEFT JOIN Milk_Products_SL MPSL
ON MPN.Product_ID = MPSL.Product_ID
WHERE MPSL.[Shelf_Life (in days)] (greaterthan) 4
--Step 5
Select MPN.Product_ID, MPN.Product_Name, MPSL.[Shelf_Life (in days)]
From Milk_Products_Name MPN LEFT JOIN Milk_Products_SL MPSL
ON MPN.Product_ID = MPSL.Product_ID
AND MPSL.[Shelf_Life (in days)] (greaterthan) 4
SQL Interview Problem: Splitting Full Names into First, Middle, and Last Names
• SQL Interview Problem: Splitting Full Name...
Top SQL Interview Questions: RANK, DENSE_RANK, ROW_NUMBER Explained
• Top SQL Interview Questions: RANK, DENSE_R...
Top 3 Products by Sales & Employees by Salary | SQL Ranking Functions Explained
• Top 3 Products by Sales & Employees by Sal...
SQL Interview Challenge: Find Hidden Likes in Your Friends' Favorites!
• SQL Interview Challenge: Find Hidden Likes...
SQL Magic: Aggregating Marks & Delivery Dates with Advanced Queries!
• SQL Magic: Aggregating Marks & Delivery Da...
Top 20% vs Bottom 20% of Students in SQL: NTILE vs TOP PERCENT
• Top 20% vs Bottom 20% of Students in SQL: ...
SQL Tricks: Find the 3rd Lowest Salary in Each Department with Two Powerful Methods!
• SQL Tricks: Find the 3rd Lowest Salary in ...
SQL Tutorial: Find Employees Who Joined Before Their Managers
• SQL Tutorial: Find Employees Who Joined Be...
SQL Tutorial: Retrieve Specific Rows with OFFSET & ROW_NUMBER - Top 2 Methods Compared!
• SQL Tutorial: Retrieve Specific Rows with ...
How to Build a Location-Based Hierarchy in SQL | Recursive Query Tutorial
• How to Build a Location-Based Hierarchy in...
How to Count Weekends in Any Month Using SQL Server: A Step-by-Step Guide
• How to Count Weekends in Any Month Using S...
SQL Date Magic: Find & Format the Last Day of the Previous Month
• SQL Date Magic: Find & Format the Last Day...
How to Count Workdays in SQL: Two Effective Methods Explained!
• How to Count Workdays in SQL: Two Effectiv...
How to Find Employees Earning More Than Their Managers in SQL
• How to Find Employees Earning More Than Th...
#SQL
#Database
#SQLTutorial
#SQLJoins
#LEFTJOIN
#SQLFiltering
#SQLWHEREClause
#SQLONClause
#SQLTips
#DataAnalysis