Optimizing SQL join single column with multiple columns in another table

Question

I have two tables.

common_products

id
product
owner_uid
backup_uid
manager_uid

ss_users

userID
firstName
lastName
email

I want to get a name/email list of all the owners, backups and managers.

I am using the query below, but was wondering if there was a more efficient way to go about querying the tables.

WORKING QUERY:

SELECT DISTINCT email,
                ( firstName + ' ' + lastName ) AS userFull,
                lastName
FROM   common_products cp
       LEFT OUTER JOIN ss_users u
                    ON u.userID = cp.owner_uid
UNION
SELECT DISTINCT email,
                ( firstName + ' ' + lastName ) AS userFull,
                lastName
FROM   common_products cp
       LEFT OUTER JOIN ss_users u
                    ON u.userID = cp.backup_uid
UNION
SELECT DISTINCT email,
                ( firstName + ' ' + lastName ) AS userFull,
                lastName
FROM   common_products cp
       LEFT OUTER JOIN ss_users u
                    ON u.userID = cp.manager_uid

Is there a more optimized way to query the database?

SQL is declarative. A SQL statement doesn't have performance until it is optimised and executed by a RDBMS. Which one are you using? — Martin Smith
– Martin Smith, Commented Dec 19, 2014 at 18:36
You could speed your query by changing the left outer join to just join (or inner join). There is no reason that you should care about NULL values in the select. — Gordon Linoff
– Gordon Linoff, Commented Dec 19, 2014 at 18:39
I added the tag that it is the SQL Server. I will go ahead and change the query to use just join. — Shawn Dibble
– Shawn Dibble, Commented Dec 19, 2014 at 19:23

Gordon Linoff · Accepted Answer · 2014-12-19 18:38:50Z

3

I suspect that this version might be faster:

select u.email, (u.firstName+ ' '+u.lastName) AS userFull, u.lastName
from ss_users u
where exists (select 1 from common_products cp where u.userID = cp.owner_uid) or
      exists (select 1 from common_products cp where u.userID = cp.backup_uid) or
      exists (select 1 from common_products cp where u.userID = cp.manager_uid);

Then for best performance add three indexes: common_products(owner_uid), common_products(backup_uid), and common_products(manager_uid).

This will eliminate the duplicate elimination (because you are using union) and the exists should be at least as fast as the joins.

answered Dec 19, 2014 at 18:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

FuzzyTree Over a year ago

would it be faster to have the or conditions in a single exists subquery i.e. where u.userID in (cp.owner_uid,cp.backup_uid,cp.manager_uid) or would that prevent the db from using indexes?

Gordon Linoff Over a year ago

@FuzzyTree . . . Doubtful. The above will resolve to three index lookups. Using or (or in) in a subquery could confuse the optimize on the use of indexes.

Shawn Dibble Over a year ago

Thank you. This is what I was looking to make. This is actually quite nice and incredibly fast. @FuzzyTree I tried your suggestion, but noticed that I was missing a few records.

FuzzyTree Over a year ago

@ShawnDibble that's strange because i think the query is logically equivalent. does your subquery look like this where exists (select 1 from common_products cp where u.userID in (cp.owner_uid,cp.backup_uid,cp.manager_uid))

Shawn Dibble Over a year ago

@FuzzyTree I misunderstood your suggestion. I am getting the correct number of results now. And it appears to be as quick as Gordon's suggestion (but my data is not that large, so don't hold me to that for larger data sets).

Dan · Accepted Answer · 2014-12-19 18:38:42Z

0

I'm going to simplify it, but the JOIN is the important part. I'll leave it to you to tweak the SELECT part.

SELECT DISTINCT owner.email AS owner_email, backup.email AS back_email, manager.email AS man_email
FROM common_product cp LEFT JOIN ss_users owner on owner.userID = cp.owner_uid
LEFT JOIN ss_users backup on backup.userID = cp.backup_uid
LEFT JOIN ss_users manager on manager.userID = cp.manager_uid

answered Dec 19, 2014 at 18:38

Dan

11.2k5 gold badges26 silver badges36 bronze badges

1 Comment

Shawn Dibble Over a year ago

I tried something like this, however I needed a single column of emails that I could cycle through. The query is actually built via PHP depending on if I need owner_email, backup_email, manager_email or any combination of the 3.

UnhandledExcepSean · Accepted Answer · 2014-12-19 19:25:54Z

Ensure there are indexes on common_products's owner_uid, backup_uid, and manager_uid fields as well as ss_users's userID field and you could improve performance a bit further by including the columns needed on the index.

SELECT DISTINCT
    user_owner.email [OwnerEmail],user_owner.firstName + ' ' + user_owner.lastName [OwnerUserFull], user_owner.lastName [OwnerLastName],
    user_backup.email [BackupEmail],user_backup.firstName + ' ' + user_backup.lastName [BackupUserFull], user_backup.lastName [BackupLastName],
    user_manager.email [ManagerEmail],user_manager.firstName + ' ' + user_manager.lastName [ManagerUserFull], user_manager.lastName [ManagerLastName]
FROM   common_products cp
LEFT OUTER JOIN ss_users user_owner ON user_owner.userID = cp.owner_uid
LEFT OUTER JOIN ss_users user_backup ON user_backup.userID = cp.backup_uid
LEFT OUTER JOIN ss_users user_manager ON user_manager.userID = cp.manager_uid

Intelligent Shade of Blue · Accepted Answer · 2014-12-19 19:39:44Z

-1

It's been a while since I've practised by SQL fu, but I think this should work:

SELECT DISTINCT email,
    (firstName+ ' '+lastName) AS userFull,
    lastName 
FROM common_products cp 
INNER JOIN  ss_users u 
ON (u.userID = cp.owner_uid OR u.userID = cp.backup_uid OR u.userID = cp.manager_uid)

edited Dec 19, 2014 at 19:39

answered Dec 19, 2014 at 18:39

Intelligent Shade of Blue

6,48828 silver badges56 bronze badges

2 Comments

Eric Hauenstein Over a year ago

This is more efficient?

Intelligent Shade of Blue Over a year ago

It's more efficient in a sense that there is only one query that needs to run as opposed to 3 queries that are then union'd. Plus it's less "code" duplication and less typing. :) However, query planners in modern SQL engines (Postgres, MSSQL, Oracle, etc) are intelligent enough to figure these things out. I don't currently have an SQL set up, but the OP can simply run various queries and see how long each one runs.

Collectives™ on Stack Overflow

Optimizing SQL join single column with multiple columns in another table

common_products

ss_users

4 Answers 4

5 Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

common_products

ss_users

4 Answers 4

5 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related