An unspecified joining query *is* a left join

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... ;^)

Welcome to IWETHEY!