OR in JOIN condition

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  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

• Roland Alexander STL

  SSCrazy

  Points: 2915

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  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?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  December 11, 2012 at 2:20 pm

  #1567618

  Thanks, guys. Very helpful!

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