Post reply

OR in JOIN condition

  • December 11, 2012 at 9:02 am

    #267911

    Hi,

    I have a FULL OUTER JOIN with an OR condition that's running very slowly. The reason is because I'm trying to join a users table (id, username1, username2) where one person may have two usernames with a systems table (username, system). I'm using a join like:

    SELECT *

    FROM users a

    FULL OUTER JOIN

    users_systems b ON ((a.username1 = b.username) OR (a.username2 = b.username))

    I know the full outer join isn't the most efficient join, but I need it for a different reason. It's the OR that I believe is causing my query to run on for a long time. Is there a more efficient approach to using an OR in a join like I'm trying to do? I hope that make sense; If not, I can clarify.

    Thanks,

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • December 11, 2012 at 9:08 am

    #1567495

    Yes, that will make for a slow-running query. Try writing two separate queries, one to check for each condition, then UNION ALL to combine the records.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • December 11, 2012 at 9:10 am

    #1567497

    mikes84 (12/11/2012)

    Hi,

    I have a FULL OUTER JOIN with an OR condition that's running very slowly. The reason is because I'm trying to join a users table (id, username1, username2) where one person may have two usernames with a systems table (username, system). I'm using a join like:

    SELECT *

    FROM users a

    FULL OUTER JOIN

    users_systems b ON ((a.username1 = b.username) OR (a.username2 = b.username))

    I know the full outer join isn't the most efficient join, but I need it for a different reason. It's the OR that I believe is causing my query to run on for a long time. Is there a more efficient approach to using an OR in a join like I'm trying to do? I hope that make sense; If not, I can clarify.

    Thanks,

    Mike

    You could try a UNION to see whether that helps:

    SELECT c1, c2

    FROM users a

    FULL OUTER JOIN

    users_systems b ON ((a.username1 = b.username)

    UNION ALL

    SELECT c1, c2

    FROM users a

    FULL OUTER JOIN

    users_systems b ON ((a.username1 = b.username)

    OR (a.username2 = b.username))

    Also, as I'm sure you know, SELECT * is not effecient.

    Finally, have you got appropriate indexes on the users_systems and users tables?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 11, 2012 at 2:20 pm

    #1567618

    Thanks, guys. Very helpful!

    Mike Scalise, PMP
    https://www.michaelscalise.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply