Not Sure How NOT EXISTS works

Question

This is just homework currently, but I am having trouble writing a SELECT statement for this question:

Produce a result set showing department name, department description, employee first name, and employee last name for ALL departments, including those for whom no employee has been assigned. Sort by department name.

I believe I have the SELECT, FROM, WHERE, and ORDER BY down, but the NOT EXISTS is where I am struggling.

Here is the table:

the table

SELECT deptName, deptDesc, empFirstName, empLastName
FROM department, employee
WHERE department.deptID=employee.deptID
AND NOT EXISTS (
    SELECT deptName, deptDesc
    FROM 
ORDER BY deptName ;

At this point I am just trying to include those for whom no employee has been assigned.

foobarbaz foobarbaz · Accepted Answer · 2019-04-20T23:44:16

I believe you are looking for a LEFT JOIN instead: https://www.w3schools.com/sql/sql_join_left.asp

You want to include everything from department, and also anything that matches from employee, but not just the intersection of the two.

NOT EXISTS will just return a boolean true or false if that inner query produces results with at least one row. I don't think that's what you want.

SELECT deptName, deptDesc, empFirstName, empLastName
FROM department
LEFT JOIN employee on department.deptID=employee.deptID
ORDER BY deptName;

Not Sure How NOT EXISTS works

1 Answers