if I want to find a user_id from table 1 which does not exist in the
table 2(possible multiple entries for any user_id). the user_id in
table 2 is a subset from table 1.
if I can not use subquery and minus, how can I do it?
thanks.

jzhang0502 [at] gmail.com wrote:
> if I want to find a user_id from table 1 which does not exist in the
> table 2(possible multiple entries for any user_id). the user_id in
> table 2 is a subset from table 1.
> if I can not use subquery and minus, how can I do it?

Use an outer join. Where there is no match, there you have a user_id
that does not exist in table2.

SELECT t1.user_id
FROM table1 AS t1
LEFT OUTER JOIN table2 AS t2 ON t1.user_id = t2.user_id
WHERE t2.user_id IS NULL;

Regards,
Bill K.

thanks. Bill.
really helpful
Bill Karwin wrote:
> jzhang0502 [at] gmail.com wrote:
> > if I want to find a user_id from table 1 which does not exist in the
> > table 2(possible multiple entries for any user_id). the user_id in
> > table 2 is a subset from table 1.
> > if I can not use subquery and minus, how can I do it?
>
> Use an outer join. Where there is no match, there you have a user_id
> that does not exist in table2.
>
> SELECT t1.user_id
> FROM table1 AS t1
> LEFT OUTER JOIN table2 AS t2 ON t1.user_id = t2.user_id
> WHERE t2.user_id IS NULL;
>
> Regards,
> Bill K.