Question on LEFT JOIN

Post #28,137 by drewk 2/12/02 2:46:08 PM Reply	Question on LEFT JOIN I'm getting two different stories and don't know which is right. Consider the following two choices: SELECT field_a, field_b FROM table_a, table_b WHERE table_a.id = table_b.id SELECT field_a, field_b FROM table_a LEFT JOIN table_b USING(id) Now, assuming there are other costraint rules to ensure you can't have records in table_a without matching records in table_b -- IOW the results of the above queries will always be the same -- is there any general rule about which will be faster? We have to fight the terrorists as if there were no rules and preserve our open society as if there were no terrorists. -- [link\|http://www.nytimes.com/2001/04/05/opinion/BIO-FRIEDMAN.html\|Thomas Friedman]
Post #28,140 by kmself 2/12/02 3:03:37 PM Reply	An unspecified joining query is a left join ...what I'm not sure about is the USING keyword, which I'm not familiar with. My suggestion would be: try it. See which is faster in practice. And remember There's More Than One Way To Do It™. `-- Karsten M. Self [link\|mailto:kmself@ix.netcom.com\|kmself@ix.netcom.com] [link\|http://kmself.ix.netcom.com/\|[link\|http://kmself.ix.netcom.com/\|http://kmself.ix.netcom.com/]] What part of "gestalt" don't you understand?`
Post #28,141 by drewk 2/12/02 3:10:53 PM Reply	Hmm, I don't think so BTW "using" is a MySQL syntax. Same as 'ON table_a.id = table_b.id'. An unspecified join will only return rows where there are records in both tables matching on the given terms. A left join will return all rows from the first table, with null values for all the fields from the second table where there is no mathing record. And as for trying it, I'm actually doing joins across 5 tables, with millions of records in some of them. And we don't have a development environment with a comparable volume of data. (I know, I know, I'm working on it.) So the only way to test it is to issue the queries against the production box and see if it falls over. We have to fight the terrorists as if there were no rules and preserve our open society as if there were no terrorists. -- [link\|http://www.nytimes.com/2001/04/05/opinion/BIO-FRIEDMAN.html\|Thomas Friedman]
Post #28,160 by CRConrad 2/12/02 5:38:08 PM Reply	No, "JOIN [tables] ON [condition]" is Standard SQL-92 AFAIK.
Post #28,182 by static 2/12/02 10:17:34 PM Reply	Left Join. The , syntax does an Inner Join. This is different from a Left Join. You should list the tables in order of size, smallest to largest. Select ... From smallest Inner Join small Inner Join medium Inner Join large Inner Join largest... Wade. "All around me are nothing but fakes Come with me on the biggest fake of all!"
Post #29,078 by tablizer 2/20/02 1:07:50 PM Reply	re: inner/outer left/right joins >> The , syntax does an Inner Join. This is different from a Left Join. << I always thot that inner/outer was orthogonal to left/right. Outer joins fill in non-matches with nulls IIRC while inner skips non-matches. The left/right clause tells it which direction to join at. The default is "inner" if you don't specify inner/outer and the default is "left" if you don't specify left/right. IIRC :-) ________________ oop.ismad.com
Post #29,088 by CRConrad 2/20/02 1:58:16 PM Reply	Nope, they're really more kind'a the same thing. Bryce never fully grokked relational joins: I always thot that inner/outer was orthogonal to left/right. Outer joins fill in non-matches with nulls IIRC while inner skips non-matches. Yup. And, what does it do with those rows from one table that it "makes up" nulls for? Why, it "matches" them to those rows from the other table that there is no real match for. But which is which??? Which table does it seek (and "fake") matches _for_, and which one does it present its fakes as "coming" _from_? That's what you tell it with "`LEFT`" or "`RIGHT`" (or "`FULL`"). You can't specify outer/inner without telling it that, too -- ex- or implicitly -- so they are not orthogonal (=independent). The left/right clause tells it which direction to join at. The default is "inner" if you don't specify inner/outer and the default is "left" if you don't specify left/right. Don't think of it as a two-dimensional "grid", but a one-dimensional "strip": Draw two overlapping circles (or better yet, make them elongated, ellipses) to represent your tables. Orient them horizontally, mark them A and B if you want, and/or fill them in with different hash patterns or colours. Now, an "`INNER`" join will be the cross-hatched (and/or colour-blended) area in the middle (the inner-most one!), while the "`LEFT`" and "`RIGHT OUTER`" ones are that inner area plus the rest of one circle (can't remember which direction means which circle, though), i.e, one whole circle and stuff to match some of it from the other one; and the "`FULL OUTER`" join will be the whole shebang, both circles including their common area. If you drew them elongatedly enough (=stretched out in one dimension), you'll see that it's just one continuum of data you get to select from, and the inner/outer specification together with the left/right/full specification decides what you select, "from where to where" along your strip of paper. Or, to put it another way: If "`LEFT`" / "`RIGHT`" were orthogonal from "`OUTER`" / "`INNER`", you ought to be able to combine, say, a "`LEFT INNER`" query. But your only legal alternatives are "`LEFT OUTER`", "`RIGHT OUTER`", "`FULL OUTER`", and "`INNER`" -- so they aren't independent. HTH! IIRC :-) Well, you might be remembering it correctly... But if so, then what you're remembering is -- how you got it wrong in the first place. Sorry, but that's just the way it is. Christian R. Conrad Of course, who am I to point fingers? I'm in the "Information Technology" business, prima facia evidence that there's bats in the bell tower. -- [link\|http://z.iwethey.org/forums/render/content/show?contentid=27764\|Andrew Grygus]
Post #29,157 by tablizer 2/21/02 1:31:02 AM 2/21/02 1:54:34 PM Reply	Anybody ever do a FULL OUTER ? >> That's what you tell it with "LEFT" or "RIGHT" (or "FULL"). You can't specify outer/inner without telling it that, too -- ex- or implicitly -- so they are not orthogonal (=independent). << Okay, perhaps "orthogonal" is too strong a word in that they affect the final application of the other. However, the left/right is mostly about which direction to apply the other. IOW, They are still very different things. >> Draw two overlapping circles (or better yet, make them elongated, ellipses) to represent your tables. Orient them horizontally, mark them A and B if .... << Time to get out the construction paper........tomarrow. >>.... "LEFT INNER" query. But your only legal alternatives are "LEFT OUTER", "RIGHT OUTER", "FULL OUTER", and "INNER" -- so they aren't independent. << I always thot "LEFT INNER" simply ignored the LEFT (or RIGHT). However, I never tried it. Nor a FULL OUTER. Anybody here ever need that? I betcha did on your honeymoon. It is sort of like comparative operations (x > y). The order (left/right) does matter in general. However, equality (==) is a special case where it does not matter. "Inner" is in the same camp as equality comparisons as far as I am concerned. Thus, I conceptually consider INNER a "spacial case". ________________ oop.ismad.com Edited by tablizer Feb. 21, 2002, 01:54:34 PM EST Expand All History
Post #29,534 by static 2/24/02 12:34:51 AM Reply	I like that description. It's really good - makes the different options make sense. Thanks! Oh - and Bryce, "Left Outer" vs "Right Outer" is really a semantic convenience. You should theoretically always be able to make a Right Outer Join into a Left Outer Join by swapping the table names in the Join clause. I've not tried it, though, as probably 99% of the Joins I do are Inner. Wade. "All around me are nothing but fakes Come with me on the biggest fake of all!"
Post #29,642 by CRConrad 2/25/02 12:48:00 PM Reply	You're welcome! (Both of you... ;^)
Post #28,142 by boxley 2/12/02 3:20:15 PM Reply	been a long time but wouldnt a stripped seek be faster on tables that large. thanx, bill "If you're half-evil, nothing soothes you more than to think the person you are opposed to is totally evil." Norman Mailer
Post #28,143 by drewk 2/12/02 3:28:43 PM Reply	Gaah, just got the answer The laft join syntax forces the order the tables will be scanned. On the particular query I was doing, when a regular join was used the optimizer picked the wrong order. Rather than use the keywords that direct the optimizer what to do, they just converted it to left joins. We have to fight the terrorists as if there were no rules and preserve our open society as if there were no terrorists. -- [link\|http://www.nytimes.com/2001/04/05/opinion/BIO-FRIEDMAN.html\|Thomas Friedman]
Post #28,226 by tseliot 2/13/02 10:21:53 AM Reply	Yup. The speed depends more on the dataset You didn't tell us whether .id was a unique index for either a or b (or both)? --------------------------------- A stupid despot may constrain his slaves with iron chains; but a true politician binds them even more strongly by the chain of their own ideas;...despair and time eat away the bonds of iron and steel, but they are powerless against the habitual union of ideas, they can only tighten it still more; and on the soft fibres of the brain is founded the unshakable base of the soundest of Empires." Jacques Servan, 1767

Welcome to IWETHEY!