November 19, 2012 at 5:19 am
Hi guys,
If we include 'where' clause to the Left/Right outer joins, does it becomes inner join by default??
November 19, 2012 at 5:26 am
farooq.hbs (11/19/2012)
Hi guys,If we include 'where' clause to the Left/Right outer joins, does it becomes inner join by default??
No it does not. It does really depend on what you put into WHERE clause...
November 19, 2012 at 5:30 am
After adding left outer join between two table, No proper result found.
I mean the all the data from the left table is not retrieving but only the matching records between two tables it is retrieving
Actually, optimizing the complex query,
this is an example query was checking with
Example:
select * frompersons s left join orders od on s.p_id = od.p_id
Where od.p_id in (1,2,3,5)
November 19, 2012 at 5:45 am
your query return only those recode of where clause od.p_id in (1,2,3,5)
select * from persons s left join orders od on s.p_id = od.p_id
Where od.p_id in (1,2,3,5)
if you want all record of persons try
select * from persons s left join orders od on s.p_id = od.p_id
and od.p_id in (1,2,3,5)
November 19, 2012 at 5:47 am
Thanks!!!!
🙂
November 19, 2012 at 5:47 am
If the WHERE clause includes a condition on the outer table, as your example does, then yes, the outer join is turned into an inner join. Try making the condition part of the join predicate instead.
John
November 19, 2012 at 6:19 am
John Mitchell-245523 (11/19/2012)
If the WHERE clause includes a condition on the outer table, as your example does, then yes, the outer join is turned into an inner join. Try making the condition part of the join predicate instead.John
Not exactly always!
Again, it does depend on what exactly you put into WHERE clause!
Here are two examples:
SELECT t1.*
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t2.IdColumn = t1.IdColumn
WHERE t2.IdColumn IS NULL
and another variation:
SELECT t1.*, t2.*
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t2.IdColumn = t1.IdColumn
WHERE t2.IdColumn IS NULL OR t2.SomeOtherColumn IN (1,2,3,4)
In the above two queries the WHERE clause on OUTER table doesn't make OUTER JOIN to turn to INNER JOIN...
First example gets all records from Table 1 where no related record found in Table2 at all. The second one, does the same work as having "IN" check in LEFT JOIN itself, so all records from Table1 will be returned with related data from Table2 which also satisfy "IN" condition.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply