EXCEPT ALL equivalent in MySQL

Question

So I have a table called members and another table called group.The leader of the group is also a member

To retrieve members,who are not leaders I did the following code:

SELECT first_name, last_name, rank
FROM members
EXCEPT ALL
SELECT first_name, last_name, rank
FROM members INNER JOIN groups ON mid=leader;  --edited gid as mid 

Doing this in MySQL gives me a syntax error.What should I use for EXCEPT ALL in MySQL?

Answer 1

SELECT first_name, last_name, rank
FROM members 
LEFT OUTER JOIN groups ON gid=leader
WHERE leader is null

Not sure if leader or gid is in the groups table. The column that is in the groups table must have a null check in the where clause.

Answer 2

subquery may do, something like:

SELECT first_name, last_name, rank
FROM members
WHERE id NOT IN (
  SELECT leader
  FROM groups 
  WHERE leader = members.id
)

We need to know your table structure to help you further

Answer 3

You can try with this scenario.

SELECT 
        r.col1,r.col2 
FROM    tabel1 r 
WHERE ROW ( r.col1,r.col2 ) NOT IN( 
                              SELECT 
                                  col1,col2 
                              FROM tabel2 
                              );