[thelist] Joins across UNION statements
Bill Moseley
moseley at hank.org
Tue Oct 7 12:41:43 CDT 2008
On Tue, Oct 07, 2008 at 01:21:37PM -0400, Matt Warden wrote:
> On Tue, Oct 7, 2008 at 12:31 PM, Bill Moseley <moseley at hank.org> wrote:
> > Poking around the Internet you can find recommendations against using
> > UNIONS for performance reasons and suggestions to use a join, instead.
>
> Did YOU experience any performance issues? It's the definition of a
> view we are talking about here. If there are two ways to return the
> same result, then you can easily change the method if it turns out you
> need a performance boost. Until then, I wouldn't waste my time.
Right, and I don't care about the approach either. Sorry, I didn't
mean to say I didn't want to use a UNION.
My question is how to create a VIEW with a WHERE on each select.
Maybe the answer is I don't need to.
I've got a UNION with two WHERE statements hard-coded:
SELECT
setting
FROM
user_setting
WHERE
user = 1234;
UNION
SELECT
setting
FROM
account_setting
JOIN account ON account.id = account_setting.account
JOIN user ON user.account = account.id
WHERE
user.id = 1234;
I can remove the WHERE and create this view:
CREATE VIEW my_settings AS
SELECT
setting, user
FROM
user_setting
UNION
SELECT
setting, user.id
FROM
account_setting
JOIN account ON account.id = account_setting.account
JOIN user ON user.account = account.id;
And then I can do:
SELECT setting FROM my_settins WHERE user = 1234;
Am I not doing a UNION of ALL the rows of the two tables and then
limiting to user 1234? Perhaps it's not something to worry about
but I would think I'd rather do a UNION of two small sets first.
--
Bill Moseley
moseley at hank.org
Sent from my iMutt
More information about the thelist
mailing list