I am trying to exclude a series of test users from 'test_user' table by using NOT IN. I got another 'member' table with userID and other information Originally, the tester user table is not too long,...

Transform line-by-line values from another table to a string and insert it into NOT IN clause in SQL

I am trying to exclude a series of test users from ‘test_user’ table by using NOT IN.

test_user table

I got another ‘member’ table with userID and other information

member table

Originally, the tester user table is not too long, so people use NOT IN clause to exclude test users by listing their ID, but as the test users increase day by day, I am wondering if there is any way to keep this NOT IN () up-to-date and easy to maintain.

I am not sure if there is any solution like:

@declare test_user varchar(23) = select ID from test_user

SELECT * FROM member WHERE ID NOT IN (@test_user)

Thanks for your help!

Answer

You don’t need the variable… just insert your query directly as the not in list.

SELECT *
FROM member
WHERE ID NOT IN (SELECT ID FROM test_user);

Note: It makes life easier to use a consistent casing and layout for your queries.

Advertisement

Answer