CODEWITHSUNDEEP
mysqlsqldatabase
Dante
Dante

Reputation: 537

MySQL: using "NOT IN" clause correctly

enter image description here

The problem is :Find the employee last names for employees who do not work on any projects.

My solution is:

SELECT E.Lname 
FROM EMPLOYEE E
WHERE E.Ssn NOT IN (SELECT * 
                    FROM EMPLOYEE E,WORKS_ON W
                    WHERE E.Ssn = W.Essn);

This should subtract the ssns from employee from the essns from works_on, However I keep getting the error "Operand should contain 1 column". What does this mean and how can I correct this code?

Upvotes: 0

Views: 101

Answers (4)

mechanical_meat
mechanical_meat

Reputation: 169284

The result of a NOT IN subquery must be one value. Your subquery returns all the columns in the EMPLOYEE and WORKS_ON tables. You can use NOT EXISTS instead:

SELECT E.Lname 
FROM EMPLOYEE E
WHERE NOT EXISTS (SELECT 1 
                  FROM WORKS_ON W
                  WHERE E.Ssn = W.Essn);

The 1 could be any scalar, or it could even be NULL.

Upvotes: 1

Liudi Wijaya
Liudi Wijaya

Reputation: 956

Try this

SELECT EMPLOYEE.Lname FROM EMPLOYEE WHERE EMPLOYEE.SSN NOT IN (SELECT DISTINCT WORKS_ON.Essn FROM WORKS_ON);

Upvotes: 1

Amit Garg
Amit Garg

Reputation: 3907

SELECT E.Lname 
FROM EMPLOYEE E
WHERE E.Ssn NOT IN (SELECT distinct Essn 
                    FROM WORKS_ON);

Upvotes: 1

Chuck
Chuck

Reputation: 148

SELECT E.Lname 
FROM EMPLOYEE E
WHERE E.Ssn NOT IN (SELECT E.Ssn 
                    FROM EMPLOYEE E,WORKS_ON W
                    WHERE E.Ssn = W.Essn);

not in , means not include the another value sets. So, in the next query, it should select the value set.

SELECT E.Ssn 
FROM EMPLOYEE E,WORKS_ON W
WHERE E.Ssn = W.Essn

this part will be return all of the employee's SSN who has worked.

hope it explain your questions

Upvotes: 1

Related Questions