I'm using PostGreSQL so I don't know how this tidbit will apply to most of the folks here, but just wanted to share that UNIONs are worth considering instead of disjunctions in your where/join clause. However the UNIONs aren't as simple to read I think.
Sample query before: (estimated cost : 10503 units)
Code: Select all
SELECT DISTINCT peopleid, firstname, lastname, organization
FROM people
JOIN couples ON (peopleid = leader OR peopleid = follower)
ORDER BY lastname, firstname;Code: Select all
SELECT DISTINCT p1.peopleid AS peopleid,
p1.firstname AS firstname,
p1.lastname AS lastname,
p1.organization AS organization,
coupleid,
follower AS partner,
p2.lastname AS plast,
p2.firstname AS pfirst,
'Leading'::TEXT AS role
FROM people AS p1
JOIN couples ON (p1.peopleid = leader)
FROM people AS p2 ON (p2.peopleid = follower)
UNION
SELECT DISTINCT p3.peopleid AS peopleid,
p3.firstname AS firstname,
p3.lastname AS lastname,
p3.organization AS organization,
coupleid,
leader AS partner,
p4.lastname AS plast,
p4.firstname AS pfirst,
'Following'::TEXT AS role
FROM people AS p3
JOIN couples ON (p3.peopleid = follower)
FROM people AS p4 ON (p4.peopleid = leader)
ORDER BY lastname,firstname,plast,pfirst;Yes this is basically just a "Hey that's cool" post