How do I exclude or negate two queries?

I am new to SQL, so this is probably very simple, however, I wasn't able to find the solution.

Basically my query is as follows:

SELECT UserID
FROM Users

NOT UNION

SELECT UserID
FROM User_Groups
WHERE GroupID = '$_[0]'

However, I am not sure w开发者_StackOverflow社区hat the syntax is to exclude one query from another. What I am trying to say is give me all the user ID's except for those that are in group X.

SELECT UserID FROM Users
  WHERE UserID NOT IN (SELECT UserID FROM User_Groups WHERE GroupID = ?)

P.S. Don't interpolate variables into your queries as this can lead to SQL injection vulnerabilities in your code. Use placeholders instead.

SELECT Users.UserID
FROM Users
LEFT JOIN User_Groups ON Users.UserID = User_Groups.UserID
WHERE Users.GroupID = '$_[0]'
AND User_Groups.UserID IS NULL

You can left join to the other table and then put an IS NULL check on the other table in you WHERE clause as I've shown.

You could use EXCEPT as well:

SELECT UserID
FROM Users
    EXCEPT
SELECT UserID
FROM User_Groups
WHERE GroupID = '$_[0]'

EXCEPT is SQL's version of set subtraction. Which of the various approaches (EXCEPT, NOT IN, ...) you should use depends, as usual, on your specific circumstances, what your database supports, and which one works best for you.

And eugene y has already mentioned the SQL injection issue with your code so I'll just consider that covered.

I linked to the PostgreSQL documentation even though this isn't a PostgreSQL question because the PostgreSQL documentation is quite good. SQLite does support EXCEPT:

The EXCEPT operator returns the subset of rows returned by the left SELECT that are not also returned by the right-hand SELECT. Duplicate rows are removed from the results of INTERSECT and EXCEPT operators before the result set is returned.

NOT IN() - Negating IN()

SELECT UserID FROM User_Groups WHERE GroupID NOT IN('1','2')

The IN() parameter can also be a sub-query.

Are you looking for a solution to be used with a postgres or a mySQL database? Or are you looking for a plain SQL solution?

With postgres a subquery with "WHERE NOT EXISTS" might work like:

SELECT * FROM (SELECT * FROM SCHEMA_NAME.TABLE_NAME) WHERE (NOT EXISTS (SELECT * FROM SCHEMA_NAME.TABLE_NAME)