Zend_Select and the USING join keyword

[Daniel Rossi-2]

When I try to create a join but in the column using a "USING"
expression, join and joinInner generate a ON keyword. Any ideas around
this ?

[Bill Karwin from Zend]

I think I see where you're going with this.  I assume you are currently
doing this:

$select = $db->select()
  ->from('table1')
  ->join('table2', 'table1.column1 = table2.column1');

And you're prefer to do this:

$select = $db->select()
  ->from('table1')
  ->joinUsing('table2', 'column1');

The two queries should produce the same result, assuming that your join
condition in the first example is an equality between two columns that
have the same name in both tables.  Let's open a feature request for
this:  http://framework.zend.com/issues/browse/ZF-1845

Also, you might like to use joinNatural(), which would be even more
concise.  It is like USING, but automatically makes an equi-join using
all columns that have the same name in both tables.  Here's how you do
it in Zend_Db_Select:

$select = $db->select()
  ->from('table1')
  ->joinNatural('table2');

Both USING and NATURAL JOIN syntax forms are ANSI SQL standard, but do
all our supported database brands support these forms?

Supports USING:
MySQL = yes; Oracle = yes; DB2 = yes; SQLite = yes; PostgreSQL = yes; MS
SQL Server = no.

Supports NATURAL JOIN:
MySQL = yes; Oracle = yes; DB2 = no; SQLite = yes; PostgreSQL = yes; MS
SQL Server = no.

Regards,
Bill Karwin

> -----Original Message-----
> From: Dan Rossi [mailto:spam@...]
> Sent: Monday, August 13, 2007 3:08 AM
> To: Zend Framework
> Subject: [fw-general] Zend_Select and the USING join keyword
>
> When I try to create a join but in the column using a "USING"
> expression, join and joinInner generate a ON keyword. Any
> ideas around this ?
>

[Daniel Rossi-2]

Bill Karwin wrote:

> I think I see where you're going with this.  I assume you are currently
> doing this:
>
> $select = $db->select()
>   ->from('table1')
>   ->join('table2', 'table1.column1 = table2.column1');
>
> And you're prefer to do this:
>
> $select = $db->select()
>   ->from('table1')
>   ->joinUsing('table2', 'column1');
>
> The two queries should produce the same result, assuming that your join
> condition in the first example is an equality between two columns that
> have the same name in both tables.  Let's open a feature request for
> this:  http://framework.zend.com/issues/browse/ZF-1845
>  

Something like that yes. I generally have the join columns the same
name, and then set foreign keys to them aswell. It would be nice if they
were picked up automatically.

> Also, you might like to use joinNatural(), which would be even more
> concise.  It is like USING, but automatically makes an equi-join using
> all columns that have the same name in both tables.  Here's how you do
> it in Zend_Db_Select:
>
> $select = $db->select()
>   ->from('table1')
>   ->joinNatural('table2');
>
> 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.

[Mike Fern]

>On 8/14/07, Bill Karwin <bill.k@...> wrote:
> I think I see where you're going with this.  I assume you are currently
> doing this:
>
> $select = $db->select()
>   ->from('table1')
>   ->join('table2', 'table1.column1 = table2.column1');
>
> And you're prefer to do this:
>
> $select = $db->select()
>   ->from('table1')
>   ->joinUsing('table2', 'column1');
>
> The two queries should produce the same result, assuming that your join
> condition in the first example is an equality between two columns that
> have the same name in both tables.  Let's open a feature request for
> this:  http://framework.zend.com/issues/browse/ZF-1845
>
> Also, you might like to use joinNatural(), which would be even more
> concise.  It is like USING, but automatically makes an equi-join using
> all columns that have the same name in both tables.  Here's how you do
> it in Zend_Db_Select:
>
> $select = $db->select()
>   ->from('table1')
>   ->joinNatural('table2');
>

Hi Bill,
I might miss the changelog, but I'd like to ask since when is
"joinUsing" implemented?
In ZF 1.0.0, I took a look at Zend/Db/Select.php and I can only find
joinNatural. joinUsing is not there.

Regards,
Mike

[Bill Karwin from Zend]

> -----Original Message-----
> From: Mike Fern [mailto:bloodyveins@...]
> I might miss the changelog, but I'd like to ask since when is
> "joinUsing" implemented?
> In ZF 1.0.0, I took a look at Zend/Db/Select.php and I can
> only find joinNatural. joinUsing is not there.

Right -- I meant that Dan Rossi would like to use such a method, but it
is currently not implemented.  Hence the feature request in JIRA.
Apologies that this wasn't clear.

Regards,
Bill Karwin

[Daniel Rossi-2]

Well I would prefer to use DataObjects if its not too intensive which
recognize the joins via foreign keys and not have to worry about running
joins with Zend_DB_Select :)

Bill Karwin wrote:

>> -----Original Message-----
>> From: Mike Fern [mailto:bloodyveins@...]
>> I might miss the changelog, but I'd like to ask since when is
>> "joinUsing" implemented?
>> In ZF 1.0.0, I took a look at Zend/Db/Select.php and I can
>> only find joinNatural. joinUsing is not there.
>>    
>
> Right -- I meant that Dan Rossi would like to use such a method, but it
> is currently not implemented.  Hence the feature request in JIRA.
> Apologies that this wasn't clear.
>
> Regards,
> Bill Karwin
>
>  

[Mike Fern]

>On 8/14/07, Bill Karwin <bill.k@...> wrote:
>
> Right -- I meant that Dan Rossi would like to use such a method, but it
> is currently not implemented.  Hence the feature request in JIRA.
> Apologies that this wasn't clear.
>
> Regards,
> Bill Karwin
>

Oh.. It seems it was me overlooking some phrases  in the post. Thanks
for clearing it up.

Regards,
Mike

[Bill Karwin from Zend]

Zend_Db_Table does not support joins of any type and there is no plan to
change this.

If you need to do joins, your choices are Zend_Db_Select or
Zend_Db_Adapter.

Regards,
Bill Karwin

> -----Original Message-----
> From: Dan Rossi [mailto:spam@...]
> Sent: Monday, August 13, 2007 8:18 PM
> To: Bill Karwin
> Cc: Zend Framework
> Subject: Re: [fw-general] Zend_Select and the USING join keyword
>
> Well I would prefer to use DataObjects if its not too
> intensive which recognize the joins via foreign keys and not
> have to worry about running joins with Zend_DB_Select :)

[Bill Karwin from Zend]

> -----Original Message-----
> From: Dan Rossi [mailto:spam@...]
> > 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

[Daniel Rossi-2]

That wasnt the answer to the question, I know this, it was just a
question on natural join, im assuming natural join though isnt too
portable but very clean sql for join columns that are already setup that
way. We use mysql anyway.


Bill Karwin wrote:

>> -----Original Message-----
>> From: Dan Rossi [mailto:spam@...]
>>    
>>> 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
>
>