-1

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!

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ryan
  • 3
  • 2
  • 4
    Please don't use images for data, instead use DDL+DML statements for sample starting data, and formatted text for expected results. – Dale K Dec 27 '20 at 04:08
  • 1
    Don’t over think it. Unless the performance degrades excessively, do nothing. – Bohemian Dec 27 '20 at 04:17
  • Thanks for your comment. When you mention "DDL+DML" statement, do you mean CREATE TABLE table_name (....)? for the expected results, could you kindly share an example? Thanks again! – Ryan Dec 27 '20 at 04:17
  • Create a temp table, and insert sample data - that way people can copy and paste for testing. Expected results can be just formatted text. You should be doing that yourself as part of creating a [mre] - and its not uncommon to solve your own problem while building the MRE. Here is [an example](https://stackoverflow.com/questions/61584744/select-top-rows-until-value-in-specific-column-has-appeared-twice) – Dale K Dec 27 '20 at 04:35
  • @DaleK thanks! I will follow this example in the future. Also, your solution works perfectly. Thank you so much. – Ryan Dec 28 '20 at 22:02

3 Answers3

1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
1

You can use NOT EXISTS as follows:

SELECT * FROM MEMBER M
WHERE NOT EXISTS 
  (SELECT 1 FROM TESTUSER T 
    WHERE T.ID = M.ID)

OR You can use the LEFT JOIN as follows:

SELECT M.* 
  FROM MEMBER M LEFT JOIN TESTUSER T ON T.ID = M.ID
 WHERE T.ID IS NULL;
Popeye
  • 35,427
  • 4
  • 10
  • 31
0

Are you able to modify the member table? If so, you can add a new column is_test_user. Then you can query using

SELECT *
FROM member
WHERE is_test_user = 0

If you do this, I recommend adding an index on is_test_user as well.

Edit: Since the question had been updated, my answer no longer fits.

Edit 2: Removed the index recommendation per Bohemian's suggestion.

Karn Ratana
  • 166
  • 4
  • 2
    Adding an index on a boolean column will have no effect due to lack of sufficient cardinality. However, there may be a small performance increase if there is a covering index on both id and is_test_user. – Bohemian Dec 27 '20 at 04:15
  • hi Karn, unfortunately, I cannot modify the member table. Thanks for your reply! – Ryan Dec 27 '20 at 04:19
  • @Bohemian thanks for the suggestion. I assumed that the number of test users would be a small percentage, where the index could be useful. Which might or might not be correct. In any case, OP cannot modify the member table. – Karn Ratana Dec 27 '20 at 04:26
  • 1
    You'd still need a way of keeping the table updated, commonly triggers, which could easily overcomplicate the situation. – Dale K Dec 27 '20 at 04:36