Creating a view with a 'WHERE NOT IN' SQL subquery

Question

I'm trying to turn the following SQL query into a view.

SELECT * FROM system_accounts 
WHERE system_accounts.id 
NOT IN (SELECT account_id 
        FROM system_group_members
        WHERE system_group_members.group_id = 1);

In other words, how do I go about creating a view that gives me all the non-members of a certain group, with those non-members coming from the total set of accounts known to the system?

The query works fine when I test it in my Adminer window. However, I'm at a loss how to express the variable group_id (which in my example is '1') in correct SQL for a view.

I'm sure I'm missing something trivial, but this is the sort of thing I'll bang my head on for hours. Hopefully some kind soul here will help me out.

Many thanks for your time.

Views [cannot be parametrized](https://stackoverflow.com/questions/1687279/can-we-pass-parameters-to-a-view-in-sql). Search for table-valued function. Second if `account_id` is nullable your query may not return any rows — Lukasz Szozda, Dec 05 '20 at 15:01
Many thanks for your succinct answer. I will endeavour to nibble at my problem from a different angle :) — userwithname, Dec 05 '20 at 15:23
Beware of `NOT IN`. It doesn't do what you think it does, if there are nulls in the data. Better use an anti-join and sleep well. — The Impaler, Dec 05 '20 at 15:42

score 0 · Answer 1 · answered Dec 05 '20 at 16:21

You can create a view that has all non-members of all groups. Then you can filter down to the group you want:

CREATE VIEW v_nonmembers AS
    SELECT g.group_id, sa.*
    FROM system_groups g cross join
         system_accounts sa LEFT JOIN
         system_group_members sgm
         ON gsm.group_id = g.group_id AND
            gsm.account_id = sa.id
    WHERE gsm.group_id IS NULL;

You can then use it as:

SELECT *
FROM v_nonmembers
WHERE group_id = 1;

Creating a view with a 'WHERE NOT IN' SQL subquery

1 Answers1