Business impact of inner join instead of left join
Hallo experts,
"Is there any business impact in using inner join instead of left join, in SQL?"
I am sorry to say, this is not any work related question, but my boss asked me todays morning, and after much thinking, I found that this is the only place I can ask for.
Sorry again, if I irritate you, such type of idiot question
Thanx,
Soumen
"Is there any business impact in using inner join instead of left join, in SQL?"
I am sorry to say, this is not any work related question, but my boss asked me todays morning, and after much thinking, I found that this is the only place I can ask for.
Sorry again, if I irritate you, such type of idiot question
Thanx,
Soumen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, the result will change or at least result can change depending on the your db-model and the other conditions in your query
INNER JOIN: only returns a record when there is a record in both tables
LEFT JOIN: also gives a result when there is no record in the 'right' table
select *
from orderline as ol
left join order as o on o.order_id = ol.order_id
=> if there is a hiërarchical dependency (no orderline without order) then LEFT or INNER join don't make a difference
It will if you write it as
select *
from order as o
left join orderline as ol on o.order_id = ol.order_id
=> also orders are selected for which no orderline exists
Adding a 'WHERE ol.type = 1' to that last query will make that to have a result a condition in the orderline-record must be met so that condition cannot be true if here is no joined record. In this case the left join can better be written as an INNER JOIN while the 'LEFT' is just fooling yourself.
INNER JOIN: only returns a record when there is a record in both tables
LEFT JOIN: also gives a result when there is no record in the 'right' table
select *
from orderline as ol
left join order as o on o.order_id = ol.order_id
=> if there is a hiërarchical dependency (no orderline without order) then LEFT or INNER join don't make a difference
It will if you write it as
select *
from order as o
left join orderline as ol on o.order_id = ol.order_id
=> also orders are selected for which no orderline exists
Adding a 'WHERE ol.type = 1' to that last query will make that to have a result a condition in the orderline-record must be met so that condition cannot be true if here is no joined record. In this case the left join can better be written as an INNER JOIN while the 'LEFT' is just fooling yourself.
Hi,
Firstly dont be sorry... :-)
Secondly Its depend on your query that whether It can be impact on the business or not.
Normal case It wont affect on business.
Left Join can be used to track the invalid data.
e.g. Master & Child
if child is missing for master data then Left Join can be used to find Master without child.
Inner Join will wont show master data if child is missing.
-Bhavesh
Firstly dont be sorry... :-)
Secondly Its depend on your query that whether It can be impact on the business or not.
Normal case It wont affect on business.
Left Join can be used to track the invalid data.
e.g. Master & Child
if child is missing for master data then Left Join can be used to find Master without child.
Inner Join will wont show master data if child is missing.
-Bhavesh
ASKER
Thank you very much