MySQL: using "NOT IN" clause correctly

Question

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?

mechanical_meat · Answer 1 · 2016-03-01T04:18:41.700

1

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.

edited Mar 01 '16 at 04:18

answered Mar 01 '16 at 04:10

mechanical_meat

163,903
24
228
223

What is the difference between NOT IN and NOT EXISTS and what does the 1 mean here? – Dante Mar 01 '16 at 04:12
1

Check out this question and answers: http://stackoverflow.com/questions/173041/not-in-vs-not-exists – mechanical_meat Mar 01 '16 at 04:14
1

@DanteGreyson *NOT EXISTS* perform better than *IN*. – Hotdin Gurning Mar 01 '16 at 04:15

score 1 · Answer 2 · answered Mar 01 '16 at 04:12

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

score 1 · Answer 3 · answered Mar 01 '16 at 04:14

1

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

answered Mar 01 '16 at 04:14

Amit Garg

3,867
1
27
37

score 1 · Accepted Answer · answered Mar 01 '16 at 04:17

1

Try this

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

answered Mar 01 '16 at 04:17

Liudi Wijaya

898
2
8
24

You did not connect table employee to table dependent anywhere. Is that not necessary? – Dante Mar 01 '16 at 04:33
1

Because your problem is only find the employee last names for employees who do not work on any projects. – Liudi Wijaya Mar 01 '16 at 04:40

MySQL: using "NOT IN" clause correctly

4 Answers4