1 Topic by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Topic: INNER JOIN and LEFT JOIN

Just trying to get a clearer idea of how these two actually differ. Been having a read of the pgsql docs, but I'm still very little the wiser. big_smile What exactly are the real world differences? Are there certain scenarios where one or the other should not be used? Any testing I've done always seems to work just as happily with a left join as an inner, so is there a real need to use inner, (as from what I could understand of the docs, an inner join is initially made on a left join anyhow)?

Cheers in advance for any 'drum it into the muppet explanations'. big_smile


Matt

p.s: Btw, is it just me, or do some queries really need a twisted logic to get correct? big_smile

MattF's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: INNER JOIN and LEFT JOIN

http://dev.mysql.com/doc/refman/5.0/en/join.html

#

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL. See Section 6.2.8, ?LEFT JOIN and RIGHT JOIN Optimization?.

That's the major difference

  • Registered: 2006-08-16
  • Posts: 92

Re: INNER JOIN and LEFT JOIN

http://www.codinghorror.com/blog/archives/000976.html

smile

Post hoc ergo propter hoc

Tjalve's Website

4 Reply by MattF (edited by MattF 2008-01-26 20:25)

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: INNER JOIN and LEFT JOIN

So it basically gives a tad more versatility then, if I've understood that correctly? i.e: you can check for the absence of a match as well as an actual match?

Is it okay then to generally use left joins only for general straight match type queries? (Which I'm already guilty of doing upto just). big_smile


Thanks again Smartys,

Matt

MattF's Website

5 Reply by MattF

  • MattF
  • Senior Member
  • Offline
  • From: South Yorkshire, England
  • Registered: 2007-03-15
  • Posts: 1,956

Re: INNER JOIN and LEFT JOIN

Tjalve wrote:

http://www.codinghorror.com/blog/archives/000976.html

smile

Cheers. Just having a look now. Love that domain name. big_smile

MattF's Website