We help IT Professionals succeed at work.
Get Started

LEFT OUTER JOIN VS. RIGHT OUTER JOIN

tantormedia
tantormedia asked
on
523 Views
Last Modified: 2012-05-11
Dear experts,

I have a query:
SELECT ...
MAX(CASE WHEN r.r_type = 'Audio' THEN r.Awards END) AudioReviews,
MAX(CASE WHEN r.r_type = 'Print' THEN r.Awards END) PrintReviews,
...
FROM BookList b
...
LEFT OUTER JOIN
(SELECT BookStem, r_type, GROUP_CONCAT(DISTINCT CASE WHEN Award IS NOT NULL THEN
CONCAT('<a href="/manage/review.asp?review=', CAST(ID AS CHAR), '" target="_blank">', Award, '</a>')
ELSE CONCAT('<a href="/manage/review.asp?review=', CAST(ID AS CHAR), '" target="_blank">Review</a>') END SEPARATOR '<br />') Awards
FROM Reviews
GROUP BY BookStem, r_type) r
ON b.BookStem = r.BookStem

That outer join part makes my query very slow. I replace the LEFT OUTER JOIN with RIGHT OUTER JOIN and swapped tables accordingly:
SELECT ...
MAX(CASE WHEN r.r_type = 'Audio' THEN r.Awards END) AudioReviews,
MAX(CASE WHEN r.r_type = 'Print' THEN r.Awards END) PrintReviews,
...
FROM
(SELECT BookStem, r_type, GROUP_CONCAT(DISTINCT CASE WHEN Award IS NOT NULL THEN
CONCAT('<a href="/manage/review.asp?review=', CAST(ID AS CHAR), '" target="_blank">', Award, '</a>')
ELSE CONCAT('<a href="/manage/review.asp?review=', CAST(ID AS CHAR), '" target="_blank">Review</a>') END SEPARATOR '<br />') Awards
FROM Reviews
GROUP BY BookStem, r_type) r
RIGHT OUTER JOIN
BookList b
ON b.BookStem = r.BookStem
...
To my surprise, now the query runs 3 times faster, though the result seems to be the same. Could you please explain this to me? I always thought it there should be no difference.
Also, maybe you could give me any suggestion how to improve the query further?
Thanks.
Comment
Watch Question
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE