mysql/php query

Andy Hassall (4.980% quality rating):
>
> OK, but I'd argue that if you needed an outer join in this specific
> case, then the database is broken; referential integrity checks
> really belong in the database (although the real world sometimes gets
> in the way of that).

I'm not sure what you're saying here about referential integrity checks
belonging in the database. In the simple example given, if someone is
added to the people table, but no corresponding entry for them is ever
added to the favorites table, how does the database know whether or not
that is a problem (and what should it do if it is?).

> Well, It Depends. But any time you fetch more data than you need, there's a
> difference. And once you get past trivial queries, using outer joins where
> they're not needed can certainly change for the worse

I frequently use them in more complex queries, even joining more than
two tables. However, in most cases where I want a LEFT JOIN, it's
because there may be no corresponding entries in the second (and
potentially third) table(s).

But still, there are cases where there should be no good reason for the
second table to be missing the corresponding entry, but I don't want to
omit output data if the entry is somehow missing. Like in the sample
code where I was checking "users LEFT JOIN user_settings". A user might
accidentally not have had his user_settings record created... but that
doesn't mean the user doesn't exist, and so if I am trying to show a
list of users with some piece of information from their user settings, I
will do a left join to make sure I get all the users. The alternative
(if I am not willing to trust the user_settings table to have all of the
users' entries) would seem to be to do a select on the users table and
then N selects on the user_settings table, but that's clearly less
efficient (N+1 queries vs. 1 query).

> and constrain the access paths your database can use.

I don't know what this means at all.

/joe
--
The choad is wholesale. The 3LA is slimy and educational.

On Mon, 17 Nov 2003 21:27:35 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:

>Andy Hassall (4.980% quality rating):
>>
>> OK, but I'd argue that if you needed an outer join in this specific
>> case, then the database is broken; referential integrity checks
>> really belong in the database (although the real world sometimes gets
>> in the way of that).
>
>I'm not sure what you're saying here about referential integrity checks
>belonging in the database. In the simple example given, if someone is
>added to the people table, but no corresponding entry for them is ever
>added to the favorites table, how does the database know whether or not
>that is a problem (and what should it do if it is?).

But that can't happen in the data model given; favourite was part of the first
table.

The data given was:

key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1

the favorite icecream table is this:
key - flavors
1 - vanilla
2 - chocolate
3 - strawberry

Since 'favorite' was part of the 'person' row, then the only situations where
an outer join would apply would be:

(a) favorite was null (this could be a valid case for an outer join)
(b) favorite was set to a value that does not appear in the favorite table
(this was the case I was referring to as being broken, since I was assuming
'favorite' was declared as not null in the first table).

Case (b) should be caught by a foreign key constraint, and so on the
assumption that favorite is not null, there's no valid situation for an outer
join.

>> Well, It Depends. But any time you fetch more data than you need, there's a
>> difference. And once you get past trivial queries, using outer joins where
>> they're not needed can certainly change for the worse
>
>I frequently use them in more complex queries, even joining more than
>two tables. However, in most cases where I want a LEFT JOIN, it's
>because there may be no corresponding entries in the second (and
>potentially third) table(s).

Yes, that's the purpose of an outer join.

>But still, there are cases where there should be no good reason for the
>second table to be missing the corresponding entry, but I don't want to
>omit output data if the entry is somehow missing. Like in the sample
>code where I was checking "users LEFT JOIN user_settings". A user might
>accidentally not have had his user_settings record created... but that
>doesn't mean the user doesn't exist, and so if I am trying to show a
>list of users with some piece of information from their user settings, I
>will do a left join to make sure I get all the users. The alternative
>(if I am not willing to trust the user_settings table to have all of the
>users' entries) would seem to be to do a select on the users table and
>then N selects on the user_settings table, but that's clearly less
>efficient (N+1 queries vs. 1 query).

Sure, if it makes sense (or is convenient) to bring rows back where there is
no relation. On the other hand, if you're querying for a user's settings, it
doesn't necessarily make sense to bring back a row if there are no settings in
the table. That doesn't imply the user doesn't exist, just that that user has
no settings.

I see the point you're making (and even agree!), but what I'm trying (badly)
to get across was that the original question never called for an outer join,
just an ordinary join. There's no advantage to having it return one row with
the flavour field NULL, versus no rows indicating no favourites matched.

As you said on the other reply:

>Of course, if I am
>querying for information I'm not going to use (e.g., if I was going to
>ignore those rows of the result where fields came back NULL), that is a
>waste of processing.

Exactly! :-)

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

While thinking about how cool Tipper Gore was, Andy Hassall blurted:
>
> But that can't happen in the data model given; favourite was part of
> the first table.

good point, I should have checked the initial example again before
spouting.

> Case (b) should be caught by a foreign key constraint, and so on the
> assumption that favorite is not null, there's no valid situation for an outer
> join.

ah, so in a foreign key constraint I can tell the db "don't allow me to
add this person record if there is no matching flavor record for his
favorite"?

> doesn't necessarily make sense to bring back a row if there are no settings in
> the table. That doesn't imply the user doesn't exist, just that that user has
> no settings.

yes, the example I cited was actually a combined "get all users" and
"get their settings" simultaneously, so it made sense to do the left
join.

/joe
--
In the emo garage, the chair from Cuddles the Cat will go to Psi U. Cuddles
the Cat's delightful computer from Hojohoro Bekahamu will go to Sarah H..
Mike Doyle ignores the monitor from Faff.

On Mon, 17 Nov 2003 22:59:36 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:

>> Case (b) should be caught by a foreign key constraint, and so on the
>> assumption that favorite is not null, there's no valid situation for an outer
>> join.
>
>ah, so in a foreign key constraint I can tell the db "don't allow me to
>add this person record if there is no matching flavor record for his
>favorite"?

Yes - it'll raise an error that there is no parent record.

Additionally it won't let you delete the flavour record leaving 'dangling'
rows in the people table that were referencing it; it'll complain there are
child records present. Typically you can set the constraint to either raise an
error, 'on delete cascade' (deleting the child rows too), or 'on delete set
null'.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)