> -----Original Message----- > From: Dan Rossi [mailto:[EMAIL PROTECTED] > > Both USING and NATURAL JOIN syntax forms are ANSI SQL > > standard, but do all our supported database brands > > support these forms? > > Interesting. Yes I was also looking at this. Ive never used > this keyword at all. usually INNER so it collects the least > ammount of rows. Is it the same pretty much, but just > automatically detects the join columns ? > > Im assuming like foreign keys the join columns need to be the > same type.
Okay -- now we are talking about SQL, not Zend Framework specifically. INNER JOIN means that the result set contains only rows from each table that match the join condition. SELECT * FROM a INNER JOIN b ON a.col1 = b.col1 The keyword INNER is optional. So INNER JOIN and JOIN are the same thing. USING is an shorter form, but accomplishes exactly the same thing, assuming the named column exists in both tables, and the comparison operator is equality (=). SELECT * FROM a JOIN b USING (col1) USING can also take multiple column names. The following two queries achieve the same result: SELECT * FROM a JOIN b ON a.col1 = b.col1 AND a.col2 = b.col2 SELECT * FROM a JOIN b USING (col1, col2) NATURAL JOIN is even shorter. You don't have to mention the columns in the join condition at all; the query implicitly uses _all_ columns whose names appear commonly in both tables. If col1 and col2 are the only columns names both tables have in common, the following should achieve the same result as the previous query: SELECT * FROM a NATURAL JOIN b There is no requirement that the columns have the same data type, only that they are data types that can be compared with an equality operator (the limits on what data types can be compared varies by RDBMS implementation). There is no requirement that the columns are related using a foreign key constraint. Columns that are related by referential integrity are often what you want to use in join conditions, but that is not a requirement of the SQL language. Regards, Bill Karwin
