Q:

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

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?

A:

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)

