Help with LEFT JOIN

Databases
1

I have a query that runs fine as


select t1.postid, t1.forumid, l.postid, l.liketype as lpost from posts as t1 LEFT JOIN postlikes AS l ON t1.postid = l.postid

All the rows from t1 come back regardless of whether there are corresponding rows in table l.

But when I put a where condition on the l table, all I get back are rows from t1 that have corresponding records in l


select t1.postid, t1.forumid, l.postid, l.liketype as lpost from posts as t1 LEFT JOIN postlikes AS l ON t1.postid = l.postid where l.liketype = 'L'

I thought that was the way LEFT JOIN worked? How can I restrict the rows from table l and still get all the rows from t1?

2

first thing i want to teach you is to stop writing your sql all on one humoungously long single line

man, i ~hate~ scrolling sideways

so this time, i’ll do it for you –

SELECT t1.postid
     , t1.forumid
     , l.postid
     , l.liketype as lpost 
  FROM posts as t1 
LEFT OUTER
  JOIN postlikes AS l 
    ON l.postid = t1.postid
 WHERE l.liketype = 'L'

okay, that’s your query, and now i’m going to make a very small change –

SELECT t1.postid
     , t1.forumid
     , l.postid
     , l.liketype as lpost 
  FROM posts as t1 
LEFT OUTER
  JOIN postlikes AS l 
    ON l.postid = t1.postid
   AND l.liketype = 'L'

can you spot the difference?

see also this recent thread – http://www.sitepoint.com/forums/mysql-182/left-join-select-query-woe-756631.html

:cool:

3

Sorry about that. Hopefully there won’t be a next time. :slight_smile:

And yes I see the difference and it works fine. And I should have gone back to the book first but I went out to the MySQL site. In your book, you use LEFT OUTER JOIN but on the MySQL site they use LEFT JOIN. Is there a left join or does it behave just like an inner join?

4

Ahhhhhhhhhh, I see. You are attaching the ‘L’ criteria to the join. Okay, got it.

5

the word OUTER is optional. You’ve caught on, but to clarify, if you have a restriction on the data from the right hand table of a LEFT JOIN, that restriction belongs in the join clause. Putting it in a WHERE clause, it gets applied after the join and thus filters out non matching rows from the LEFT HAND (or first listed) table.

6

Thanks for the input. And I had read “around” this topic while reading the MySQL docs but couldn’t get the syntax. So once I saw it it made perfect sense. Very valuable lesson to learn. I’m getting there. :slight_smile: