Is it possible to restrict the results of an outer join?

Question

I've got a scenario where I need to do a join across three tables.

table #1 is a list of users
table #2 contains users who have trait A
table #3 contains users who have trait B

If I want to find all the users who have trait A or trait B (in one simple sql) I think I'm stuck.

If I do a regular join, the people who don't have trait A won't show up in the result set to see if they have trait B (and vice versa). But if I do an outer join from table 1 to tables 2 and 3, I get all the rows in table 1 regardless of the rest of my where clause specifying a requirement against tables 2 or 3.

Before you come up with multiple sqls and temp tables and whatnot, this program is far more complex, this is just the simple case. It dynamically creates the sql based on lots of external factors, so I'm trying to make it work in one sql. I expect there are combinations of in or exists that will work, but I was hoping for some thing simple. But basically the outer join will always yield all results from table 1, yes?

Answer 1

SELECT *
  FROM table1
LEFT OUTER
  JOIN table2
    ON ...
LEFT OUTER
  JOIN table3
    ON ...
 WHERE NOT (table2.pk IS NULL AND table3.pk IS NULL)

or if you want to be sneaky:

 WHERE COALESCE(table2.pk, table3.pk) IS NOT NULL

but for you case, i simply suggest:

SELECT *
  FROM table1
 WHERE table1.pk IN (SELECT fk FROM table2)
    OR table1.pk IN (SELECT fk FROM table3)

or the possibly more efficient:

SELECT *
  FROM table1
 WHERE table1.pk IN (SELECT fk FROM table2 UNION (SELECT fk FROM table3)

Answer 2

I think you could do a UNION here.

Answer 3

May I suggest:

SELECT columnList FROM Table1 WHERE UserID IN (SELECT UserID FROM Table2)
UNION
SELECT columnList FROM Table1 WHERE UserID IN (SELECT UserID FROM Table3)

Answer 4

If you really just want the list of users that have one trait or the other, then:

SELECT userid FROM users
  WHERE userid IN (SELECT userid FROM trait_a UNION SELECT userid FROM trait_b)

Regarding outerjoin specifically, longneck's answer looks like what I was in the midst of writing.

Answer 5

Would something like this work? Keep in mind depending on the size of the tables left outer joins can be very expensive with regards to performance.

Select *
from table1
where userid in (Select t.userid
From table1 t
left outer join table2 t2 on t1.userid=t2.userid and t2.AttributeA is not null
left outer join table3 t3 on t1.userid=t3.userid and t3.AttributeB is not null
group by t.userid)

Answer 6

If all you want is the ids of the users then

SELECT UserId From Table2
UNION
SELECT UserId From Table3

is totally sufficient.

If you want some more infos from Table1 on these users, you can join the upper SQL to Table 1:

SELECT <list of columns from Table1>
FROM Table1 Join (
    SELECT UserId From Table2
    UNION
    SELECT UserId From Table3) User on Table1.UserID = Users.UserID