-1

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?

rtys
  • 15
  • 6
  • If you have a `LEFT JOIN` and then reference the right table's columns in your `WHERE` clause without allowing `NULL` to be a valid value for them, you've transformed the `LEFT JOIN` back into an `INNER JOIN` since *only matching rows* from the right table can meet the requirements of the `WHERE` clause. – Damien_The_Unbeliever Oct 22 '21 at 10:47
  • Thanks for your answer, does that mean left join is useful when checking null values in other table – rtys Oct 22 '21 at 10:53
  • Use inner join when using left join would give the same result in the overall expression, so people know that the simpler/inner result is adequate at that point & the extra rows left join would return are not needed. But just how one uses operators to write expressions is "opinion". PS Please put your (1 specific researched non-duplicate) question in your post body not just the title. PS Please before considering posting, google many clear concise phrasings of you problem without your particular strings & line numbers, with & without 'site:stackoverflow.com'. [ask] [help] [meta] [meta.se] – philipxy Oct 22 '21 at 11:00
  • Your title asks 1 thing but later you ask something else, for help not getting confused. Confused about what? What is your 1 question? If you want just inner join rows, use inner join; if you want those rows plus the other ones left join gives, use left join. How can you get confused? What is the problem? Have you actually asked the question you mean to ask? – philipxy Oct 22 '21 at 11:58
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Oct 22 '21 at 11:58

1 Answers1

2

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. :)

mnemonic
  • 692
  • 1
  • 9
  • 18
  • Yes makes sense , thanks . In such cases which join is best to use and how to identify that? – rtys Oct 22 '21 at 10:55
  • Typically you'll use the LEFT JOIN when you're more interested in the data from the LEFT table regardless of what it links to in the RIGHT table. e.g. I want to see all the customer regardless of whether there is colour assigned to them or not. Or when you want to perform aggregates against your queries (SUM, COUNT, AVG etc.). e.g. to get the SUM of colours assigned to a Customer. In this case, if no colour is associated with a Customer the SUM will return 0 where the Colour is NULL. Does that make sense? – mnemonic Oct 22 '21 at 11:17