Inner join and left join gives same results with where filter and hence which join to use?-CodePudding

I want to fetch all customers that have ordered color pink

Customers table

cid      name
1001     Anna
1002     Boi
1003     Canny
1004     Dore

Orders Table

cid   color
1001  pink
1002  yellow
1005  green
1003  pink

I try to use left join like below

select t1.cid,t1.name,t2.color
from Customers t1
left join Orders t2
on t1.cid=t2.cid
where t2.color='pink'

I try to use inner join

 select t1.cid,t1.name,t2.color
 from Customers t1
 inner join Orders t2
 on t1.cid=t2.cid
 where t2.color='pink'

With inner join or left join I get same results with above query. I understand how these 2 joins work. Inner join will fetch only matching records from both tables and apply where filter. Left join will fetch all records from left table and then apply where filter. But I get confused which one to use for such instances like above.

There are many scenarios like these where I get confused which join to use, even though i know how to get the required results. I am bit new to SQL and if any body can help me with this?

CodePudding user response：

This happens when you apply the filter (where clause condition) on the table you're left joining on. In this case the 'Order' table.

It is because your where clause explicitly filters rows from the Order table where the color is pink. It will then join on only the matching Order rows on the Customer table.

You'll see that when you remove the where clause, the left join will function as you expect. :)