Firstly your syntax is incorrect
FROM employee and INNER JOIN department d
should be
FROM employee LEFT JOIN department d
Your next problem is that you have been told to include employees who have no associated department - which means you should not use an
INNER
join but use a
LEFT OUTER JOIN
instead. You will get a department.name of
NULL
where there is no associated department - deal with that in your display layer
Edit: In response to the comments I have returned to give a more complete (and corrected) answer.
Firstly - read up on SQL syntax. You cannot have
SELECT employee.name, department.name
if you having given your tables aliases - that
d
after department is the alias. You also cannot have
ON d.code=e.department_code
unless you also have the alias
e
- there is no alias after employee.
Next problem
WHERE e.department_code AND d.code IS NULL
e.department code is not a boolean so I guess you were trying to say
WHERE e.department_code IS NULL AND d.code IS NULL
Which it never could be because you did an inner join on those very columns. This article may help to explain why INNER join would never work in this case
Visual Representation of SQL Joins[
^]
However, my suggestion of using a LEFT OUTER join was erroneous - I should have suggested a FULL OUTER JOIN instead. Why? Because you want everything from both tables - something like this
SELECT e.name, d.name
FROM @employee e
FULL OUTER JOIN @department d
ON d.code=e.department_code
ORDER BY d.name DESC;
Note there is no WHERE clause here - we want
everything