Target SQL Interview Question | Using Sub-query | Level - MEDIUM

[@shubhamsoni174]

thankyou, these sql problem solving videos are very helpful. please bring more such videos 🙏

[@kritiknagar6089]

Thanks for making such great videos. very insightful.🙏

[@jadejagaming2051]

make video on when to use join and when to sub query i am confused in both

[@Manifestion_kannada]

Very helpful and in detail explaination

[@florincopaci6821]

Hello,
Another method is by using the exists operator:
select s.seller_name
from sellers s
where not exists (select * from orders where s.seller_id=seller_id and year(sale_date)=2020);

[@badrilalnagar9232]

Considering God as omnipresent and just, we will incorporate His discipline in our lives.

[@user-hy1ce2fq8k]

select seller_name from sellers where seller_name not in (select s.seller_name
from orders o left join sellers s on o.seller_id=s.seller_id
where year(o.sale_date) = '2020');

[@RahulKumarBEC]

Great Video , very helpful

[@omkarshinde8154]

Why use left join

[@vishalmane3139]

y nt ben

[@tilu391]

with cte as (select s.seller_id,s.seller_name,o.sale_date from sellers as s
left join orders as o on
s.seller_id=o.seller_id group by s.seller_id,s.seller_name,sale_date)
select distinct seller_name from cte where seller_name not in ( select seller_name from cte where YEAR(sale_date)=2020);

[@akhilsingh5251]

Hello Here Is my solution:
select s.seller_name from sellers S
join
(select seller_id from orrdes
where seller_id not in (
select distinct seller_id from orrdes
where YEAR(sales_date) = 2020)) A
on s.seller_id = a.seller_id

[@RAHULKUMAR-px8em]

select S.seller_name
from orders O inner join sellers S
on O.seller_id = S.seller_id
where datepart(year,O.sale_date) != 2020
order by S.seller_name asc

[@surajsinghrajput2168]

select distinct seller_name
from orders join sellers on orders.seller_id = sellers.seller_id
where orders.seller_id not in (select distinct seller_id from orders where to_char(sale_date, 'YYYY') = '2020');

[@avinashjadon4989]

with cte1 as (
select * from orders1 where year(sale_date) = '2020')
SELECT S.seller_name FROM cte1 C RIGHT JOIN sellers S ON C.seller_id = S.seller_id
WHERE C.seller_id IS NULL