-2

The following sql query selects the employee name (from employee table), their manager's name (from manager table) and their performance (from rating table). However, if an employee's manager_id is missing, then it doesn't list that employee at all when outputting rows. Is there any way around this? Probably involving joins but not too sure. Thanks in advance :) 

SELECT employee.name,
       manager.name,
       rating.performance
FROM   employee,
       manager,
       rating
WHERE  employee.manager_id = manager.id
       AND rating.employee_id = employee.id;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Programmer
  • 1,266
  • 5
  • 23
  • 44
  • 3
    Please do not use tags that do not apply to your question. I removed the database tags as it is unclear which one you are actually using. Please add the tag of *only* the database you are actually using – John Conde Apr 16 '19 at 12:03
  • 2
    You really should stop using ANSI89 Join Syntax, it was replaced by the ANSI92 syntax 27 years ago! [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). In fact, the very reason you're having the problem is because you're using 30 year old syntax. SQL Server doesn't suppprt a `LEFT JOIN` in the `WHERE` any more. – Thom A Apr 16 '19 at 12:04
  • @Larnu Yeah, this is quite a convoluted method for joining where I have to list each condition! – Programmer Apr 16 '19 at 12:05
  • 2
    Possible duplicate of [How to select rows with no matching entry in another table?](https://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table) – Nick Apr 16 '19 at 12:06
  • 1
    Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and _easier to convert to outer join_ if needed. – jarlh Apr 16 '19 at 12:10
  • @Larnu not sure – Programmer Apr 16 '19 at 12:12

2 Answers2

4

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax. In this case, you want a LEFT JOIN:

SELECT e.name, m.name, r.performance
FROM employee e LEFT JOIN
     manager m
     ON e.manager_id = m.id LEFT JOIN
     rating r
     ON r.employee_id = e.id;

Notice that this also includes table aliases to the query is easier to write and to read.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon. By the way, if I want to do the same thing with say 10 or so tables, do I just have to left join them all? And that way even null values will be displayed in rows? – Programmer Apr 16 '19 at 12:06
  • 2
    I would argue that using 1-character aliases on your tables actually makes it much harder to read, because it forces you to manually scour through your joins to figure out what "e", "m", and "r" even refer to. Easier to write? Yes. Easier to comprehend? No. – DBro Apr 16 '19 at 12:10
  • $DBro I agree, I always write it out fully – Programmer Apr 16 '19 at 12:14
  • 1
    @novice . . . New questions should be asked as *questions* not comments. Your question here is about three tables. I don't see how it extends to 10 tables. – Gordon Linoff Apr 16 '19 at 12:33
-1

By using a LEFT JOIN you get all rows of the "left" table despite not being able to "pair" with any rows in the joining tables.

SELECT
    employee.name,
    manager.name,
    rating.performance
FROM
    employee LEFT JOIN,
    manager ON employee.manager_id = manager.id LEFT JOIN
    rating ON empoyee.id = rating.employee_id
Magnus
  • 79
  • 7