[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