Master SQL Joins: Self Joins, Outer Joins, and More!
In this video, we delve into SQL joins, a fundamental concept for querying relational databases. Discover how to use various types of joins to manipulate and retrieve data effectively:
Self Join: Understand how to join a table with itself to compare rows.
Outer Joins: Learn how Left, Right, and Full Outer Joins include unmatched rows.
Inner Join: Explore how to fetch only matching rows from both tables.
Cross Join: Get to know how to produce a Cartesian product of two tables.
We use a painting sales dataset to illustrate these joins and solve practical problems like finding unpurchased paintings, calculating total sales per artist, and more.
Script available :https://shorturl.at/HcNrp
SQL Self Join | Most common interview question
• SQL Self Join | Most common interview ques...
SQL Outer Joins | Inner Join | Left Join | Right Join | Full Join | Cross Join
• SQL Joins Explained: Master INNER, LEFT, R...
-- Using the NEW Painting Sales dataset:
select * from paintings;
select * from artists;
select * from collectors;
select * from sales;
1) Fetch names of all the artists along with their painting name.
If an artist does not have a painting yet, display as "NA"
--using coalesce
select concat(a.first_name, ' ',a.last_name) as artist_name, coalesce(p.name,'NA') as painting_name
from artists a
left join paintings p on p.artist_id=a.id
--using CASE
select concat(a.first_name, ' ',a.last_name) as artist_name,
case when p.name is null then 'NA' else p.name end as painting_name
from artists a
left join paintings p on p.artist_id=a.id
--without using left join
select concat(a.first_name, ' ',a.last_name) as artist_name, coalesce(p.name,'NA') as painting_name
from paintings p
right join artists a on p.artist_id=a.id
select * from paintings;
select * from artists;
select * from collectors;
select * from sales;
2) Find collectors who did not purchase any paintings.
select concat(c.first_name, ' ',c.last_name) as collector_name, s.id
from sales s
join collectors c on c.id=s.collector_id
where s.id is null
select * from paintings;
select * from artists;
select * from collectors;
select * from sales;
3) Find how much each artist made from sales. And how many paintings did they sell.
select artist_id, sum(sales_price) as total_price
from sales
group by artist_id
select a.id,count(s.id) as no_of_paintings_sold
from artists a
left join sales s on s.artist_id=a.id
group by a.id
select b.id, coalesce(total_price,0) as total_price, no_of_paintings_sold
from (select artist_id, sum(sales_price) as total_price
from sales
group by artist_id) a
right join (select a.id,count(s.id) as no_of_paintings_sold
from artists a
left join sales s on s.artist_id=a.id
group by a.id) b on a.artist_id=b.id
--re write using simple solution
select a.id, concat(a.first_name,' ',a.last_name) as artist_name,
coalesce(sum(sales_price),0) as total_price,
count(s.id) as no_of_paintings_sold
from artists a
left join sales s on s.artist_id=a.id
group by a.id,a.first_name,a.last_name
order by a.id
select * from paintings;
select * from artists;
select * from collectors;
select * from sales;
4) Display all the available paintings and all the artist. If a painting was sold then mark them as "Sold".
and if more than 1 painting of an artist was sold then display a "**" beside their name.
-- CROSS JOIN -- returns cartesian product
-- == Joins every record of left table with every record of right table.
select * from paintings; -- 10 records
select * from artists; -- 6 records
select * from collectors;
select * from sales;
SELECT p.name as painting_name, a.first_name
from paintings p
cross join artists a
SQL for Beginners: A Complete Introduction to SQL Basics!
• SQL for Beginners: A Complete Introduction...
Master SQL Commands: DDL, Data Types & Constraints Explained!
• Master SQL Commands: DDL, Data Types & Con...
Mastering SQL Constraints: Primary Key, Unique Key, Not Null, and CHECK Explained!
• Mastering SQL Constraints: Primary Key, Un...
Understanding SQL Foreign Keys: Building Relationships Between Tables
• Understanding SQL Foreign Keys: Building R...
SQL Identity Column: Auto-Generating Unique Values for Your Tables
• SQL Identity Column: Auto-Generating Uniqu...
SQL DML & DDL Commands Explained | Insert, Update, Delete, Truncate, Drop
• SQL DML & DDL Commands Explained | Insert,...
Master Basic SQL Queries & Operators | Part 1: Fetch, Filter & Count
• Master Basic SQL Queries & Operators | Par...
SQL Functions and Queries: Aggregate, CAST, REPLACE, ROUND, GETDATE
• SQL Functions and Queries: Aggregate, CAST...
Mastering SQL INNER JOIN: Essential Queries & Examples
• Mastering SQL INNER JOIN: Essential Querie...
SQL Group By Explained with Examples | Master Aggregate Queries
• SQL Group By Explained with Examples | Mas...
Mastering SQL: Group By, Having, CASE, Order By, Join, Top, and Limit
• Mastering SQL: Group By, Having, CASE, Ord...
Introduction to SQL Normalization: 1NF (First Normal Form) - Part 1
• Introduction to SQL Normalization: 1NF (Fi...
Understanding SQL Normalization: 2NF (Second Normal Form) - Part 2
• Understanding SQL Normalization: 2NF (Seco...
Like, subscribe, and ring the bell for more SQL insights and tutorials!