Re: getting from one table listing from another

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sunday 19 August 2007, tedd wrote:

> >Not crazy, pretty standard from a database point of view;
> >
> >SELECT a.name, b.points
> >FROM table a LEFT OUTER JOIN table b
> >ON(a.name = b.name)
> >
> >This only works if the name in table a matches a name in table b.
>
> Then why use a JOIN? It's my understanding that JOINs are used to
> included unmatched rows -- am I wrong?
>
> Wouldn't this be simpler?
>
> SELECT a.name, b.points
> FROM table_name a, table_name_points b
> WHERE a.name = b.name
>
> Besides, all that JOIN stuff makes my head hurt.
>
> Cheers,
>
> tedd

There are various kinds of JOINs.  The most common you'll actually use are 
INNER JOIN and LEFT OUTER JOIN.  

You should use an explicit INNER JOIN over an implicit join (what you have 
above) for two main reasons:

1) It's clearer and more obvious what you're doing.
2) The syntax for implicit joins changed very slightly in MySQL 5, so code 
using them *may* in some circumstances, break when you upgrade.  (We 
discovered this the hard way at work when dealing with some legacy code.)
3) I believe it's more cross-database standardized (nobody expects the Spanish 
Inquisition!)

INNER JOINs return rows only if there are matching values in both tables.  
LEFT OUTER JOINs return rows if there are matches in just the first (left) 
table, and fill in NULL values for the columns from the right table if 
nothing matches.

For more information, consult the MySQL manual or a good MySQL forum.

-- 
Larry Garfield			AIM: LOLG42
larry@xxxxxxxxxxxxxxxx		ICQ: 6817012

"If nature has made any one thing less susceptible than all others of 
exclusive property, it is the action of the thinking power called an idea, 
which an individual may exclusively possess as long as he keeps it to 
himself; but the moment it is divulged, it forces itself into the possession 
of every one, and the receiver cannot dispossess himself of it."  -- Thomas 
Jefferson

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux