CODEWITHSUNDEEP
mysqlsql
uwe
uwe

Reputation: 4077

Alternatives to using a SQL UNION in this query?

I have a friends table with columns user1 and user2 and a users table with a login field.

A friend "pair" can be stored either way, for example user1=Joe and user2=Dave, or it could be user1=Dave and user2=Joe, same thing.

I want to find all friends of a user (say "Joe") that have been logged in within the last 30 days (I'm not concerned about the date format, just want to be able to do it in 1 query).

Is using a UNION the only solution, does it have the best performance?

Upvotes: 1

Views: 3310

Answers (2)

Rich O'Kelly
Rich O'Kelly

Reputation: 41757

A UNION isn't necessary here. Just select where user1='Joe' OR user2='Joe'

Upvotes: 1

Guffa
Guffa

Reputation: 700232

A union is not the only option, you can for example do like this:

select u.user
from users u
inner join friends f on u.user = case f.user1 when 'Joe' then f.user2 else f.user1 end
where f.user1 = 'Joe' or f.user2 = 'Joe'

Whether that is faster than a union depends on several things, mostly on what indexes you have.

Upvotes: 1

Related Questions