Hm, this doesn't appear to be true... OUTER JOIN != LEFT OUTER JOIN. In my version of SQLite (3.5.9), OUTER JOIN actually seems to behave either like an INNER JOIN [2], which is very confusing (a bug?). According to the SQL-92 spec [1], the following don't adhere to SQL syntax: * OUTER JOIN * NATURAL OUTER JOIN * NATURAL CROSS JOIN
The first two, because a <join type>, in order to include OUTER, must also include an <outer join type> before it, and the last, because NATURAL can only occur in <qualified join>'s, not <cross join>'s. It's probably a good idea to avoid these three... Cheers, Andrey 1. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 2. Using the data from the wikipedia page: sqlite> SELECT * FROM employees; name dept_id --------------- --------------- Rafferty 31 Jones 33 Steinberg 33 Robinson 34 Smith 34 Jasper NULL sqlite> SELECT * FROM departments; dept_id dept_name --------------- --------------- 31 Sales 33 Engineering 34 Clerical 35 Marketing sqlite> SELECT * FROM employees OUTER JOIN departments USING (dept_id); name dept_id dept_name --------------- --------------- --------------- Rafferty 31 Sales Jones 33 Engineering Steinberg 33 Engineering Robinson 34 Clerical Smith 34 Clerical sqlite> SELECT * FROM employees LEFT JOIN departments USING (dept_id); name dept_id dept_name --------------- --------------- --------------- Rafferty 31 Sales Jones 33 Engineering Steinberg 33 Engineering Robinson 34 Clerical Smith 34 Clerical Jasper NULL NULL On Tue, Apr 21, 2009 at 4:32 PM, Andrey Fedorov <[email protected]> wrote: > Got it, so - > > , == CROSS JOIN > JOIN == INNER JOIN > LEFT JOIN == LEFT OUTER JOIN == OUTER JOIN > > And NATURAL just is just shorthand for matching on same-name rows. > > Thanks for the prompt response and detailed explanation, it's very much > appreciated! > > Cheers, > Andrey > > On Tue, Apr 21, 2009 at 2:57 PM, Griggs, Donald < > [email protected]> wrote: > >> >> >> -----Original Message----- >> From: [email protected] >> [mailto:[email protected]] On Behalf Of Andrey Fedorov >> Sent: Tuesday, April 21, 2009 1:27 PM >> To: [email protected] >> Subject: [sqlite] Difference between all the joins >> >> Hi all, >> According to the join-op syntax [1], SQLite has 13 distinct join >> statements: >> >> , >> JOIN >> LEFT JOIN >> OUTER JOIN >> LEFT OUTER JOIN >> INNER JOIN >> CROSS JOIN >> NATURAL JOIN >> NATURAL LEFT JOIN >> NATURAL OUTER JOIN >> NATURAL LEFT OUTER JOIN >> NATURAL INNER JOIN >> NATURAL CROSS JOIN >> >> Are they all unique? Which are equivalent? >> >> - Andrey >> >> 1. http://sqlite.org/syntaxdiagrams.html#join-op >> _______________________________________________ >> _______________________________________________ >> _______________________________________________ >> >> Hello, Andrey, >> >> No, many are synonyms included (I think) as part of the SQL standards or >> to ease conversion from various other SQL dialects. >> >> E.g. an "unadorned" JOIN is identical to an INNER JOIN. Inner joins >> have no "left" or "right" so a LEFT JOIN can be used as a synonym for a >> LEFT OUTER JOIN. Furthermore, LEFT is a default for OUTER JOINS, so >> OUTER JOIN == LEFT JOIN == LEFT OUTER JOIN. >> >> This article looks interesting: http://en.wikipedia.org/wiki/Join_(SQL) >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
