Getting multiple out when using NOT EQUAL operator How to display name of the department managers (fname, lname) who have no dependent?

Tried this:

Select DISTINCT EMPLOYEE.Fname,EMPLOYEE.Lname 
from EMPLOYEE 
INNER JOIN DEPENDENT ON (EMPLOYEE.Ssn != DEPENDENT.Essn);
To achieve this, I did the following:

I created two tables, Employee and Dependent - note that I only created the important fields - name and SSN - you can add the details for yourself.

CREATE TABLE `Employee` 
(
  `LName` varchar(20) DEFAULT NULL,
  `SSN` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


and 

CREATE TABLE `Dependent` (
  `Name` varchar(10) DEFAULT NULL,
  `Essn` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Employee` VALUES ('Smith','12345-6789'),('Wong','33344-5555'),('Zelaya','99988-7777'),('Wallace','98765-4321'),('Mr-X','22222-3333');  -- For NULL values of Essn, see below.


and 

INSERT INTO `Dependent` VALUES ('Alice','33344-5555'),('Theodore','33344-5555'),('Joy','33344-5555'),('Abner','98765-4321'),('Michael','12345-6789'),('Alice','12345-6789'),('Elizabeth','12345-6789');


You can see that Mr-X and Zelaya (employees) have no Essn in the Dependent tables - i.e. they have no dependents. Now for the SQL:

mysql&gt; SELECT * FROM Employee e 
WHERE e.SSN 
NOT IN 
(
  SELECT DISTINCT(Essn) 
  FROM Dependent
);


Only works if the tables join on a NOT NULL field.

+--------+------------+
| LName  |       SSN  |
+--------+------------+
| Zelaya | 99988-7777 |
| Mr-X   | 22222-3333 |
+--------+------------+
2 rows in set (0.00 sec)
mysql&gt;


For NULL SSN in the Employee table, use the following query, (as pointed out by @Paul White). I also added this line to test:

mysql&gt; INSERT INTO Employee VALUES('EBorg', NULL);
Query OK, 1 row affected (0.05 sec)

SELECT * FROM Employee emp
WHERE emp.LName NOT IN
(
  SELECT DISTINCT(e.LName)  
  FROM Employee e  
  JOIN Dependent d 
  ON e.SSN = d.Essn
);


Result:

+--------+------------+
| LName  |       SSN  |
+--------+------------+
| Zelaya | 99988-7777 |
| Mr-X   | 22222-3333 |
| EBorg  | NULL       |
+--------+------------+
3 rows in set (0.00 sec)


Initially, I assumed that everyone would have an SSN, however if they
don't, then the second query will work - I'm assuming no NULL LNames :-). It's very bad practice not to have a PRIMARY KEY which would mean that the simpler query would word.

Or, for NULLs, you could just a add a UNION of my first query and SSN IS NULL thus

SELECT * FROM Employee e 
WHERE e.SSN 
NOT IN 
(
  SELECT DISTINCT(Essn) 
  FROM Dependent
)
UNION
SELECT * FROM Employee e
WHERE e.SSN IS NULL;  -- same result as above.


Following more discussion with @PaulWhite, there are two more queries which will work providing the (correct 3 line) result shown above.

SELECT e.*
FROM Employee e
WHERE NOT Exists
(SELECT 1
 FROM Dependent d
 WHERE e.SSN = d.Essn
);


And (the best one!)

SELECT  e.*
FROM Employee e
LEFT JOIN
Dependent d
ON      e.SSN = d.Essn
WHERE   d.Essn IS NULL;