lernyoung lernyoung - 18 days ago 8
MySQL Question

Why SQL NOT IN() operator not working?

I have the following sql statement and I want to fetch data from users table where user id is NOT in another table of column_one and column_two.

I dont know why this statement return an error:

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

So with this error I remove the LIMIT statement and now it says :

Operand should contain 1 column(s)

I can see SQL wants me to select only one column in the sub-query but this is not what I want.

How can I make it work avoiding all these errors and return a valid sql statement with my filtering?

This is the SQL code I tried:

SELECT u.username, u.firstname, u.lastname,u.id,u.school
FROM users u
WHERE u.id NOT IN(
SELECT user_two,user_one FROM friends
WHERE user_one !='8'
OR user_two !='8'
)

Answer

Logically You have an asimmetric use of IN operator

the two part of the in clause must contain the same number of component

You should use

SELECT u.username, u.firstname, u.lastname,u.id,u.school
    FROM users u
        WHERE u.id NOT IN( 
            SELECT  id  FROM friends 
                WHERE user_one !='8' 
                    OR user_two  !='8'
        )

or

SELECT u.username, u.firstname, u.lastname,u.id,u.school
    FROM users u
        WHERE (u.id1, u.id2) NOT IN( 
            SELECT  user_two,user_one  FROM friends 
                WHERE user_one !='8' 
                    OR user_two  !='8'
        )

or the result is impredictable