kunde kunde - 1 month ago 11
MySQL Question

Using UNION inside of NOT IN

I'm trying to make query using a

NOT IN
condition. If I use a subquery I got no problem, but when I try to use
UNION
to join results from two tables, I got an error.

This is what I'm doing:

SELECT *
FROM users
WHERE id NOT IN(
(
SELECT DISTINCT(user_id) AS id
FROM users_table_1
)
UNION
(
SELECT DISTINCT(user_id) AS id
FROM users_table_2
)
)


Is there a way to get what I want using subqueries?

Answer Source

I think there's a syntax issue in your code. Did you try to put UNION inside the subquery?

SELECT *
FROM users
WHERE id NOT IN( 
        SELECT user_id AS id
        FROM users_table_1
        UNION
        SELECT user_id
        FROM users_table_2 
)

The DISTINCT keyword is redundant (see @ypercube's comment).