Why doesn't code behave like Inner join? Where vs And in Left Join?-CodePudding

Write an SQL query to report the name and bonus amount of each employee with a bonus less than 1000.

Input: 
Employee table:
 ------- -------- ------------ -------- 
| empId | name   | supervisor | salary |
 ------- -------- ------------ -------- 
| 3     | Brad   | null       | 4000   |
| 1     | John   | 3          | 1000   |
| 2     | Dan    | 3          | 2000   |
| 4     | Thomas | 3          | 4000   |
 ------- -------- ------------ -------- 
Bonus table:
 ------- ------- 
| empId | bonus |
 ------- ------- 
| 2     | 500   |
| 4     | 2000  |
 ------- ------- 

Output: 
 ------ ------- 
| name | bonus |
 ------ ------- 
| Brad | null  |
| John | null  |
| Dan  | 500   |
 ------ -------

SELECT 
    E.NAME, 
    (B.BONUS) AS BONUS
FROM EMPLOYEE AS E LEFT JOIN BONUS AS B
ON E.EMPID = B.EMPID WHERE COALESCE(B.BONUS,0)<=1000

I have been looking at some answers and this code gives right answer, but I am confused its said that when you use where clause on left table with left Join, you query becomes/behaves like Inner join, but why isn't the code above behaving like Inner Join, can someone explain? All the resources I have looked at says that Where filter on left table behave like an inner join, i.e. always use AND condition, i.e. in this case ON E.EMPID = B.EMPID AND COALESCE(B.BONUS,0)<=1000, should work right? Can anyone explain what's happening?

CodePudding user response：

I have been looking at some answers and this code gives right answer, but I am confused its said that when you use where clause on left table with left Join, you query becomes/behaves like Inner join, but why isn't the code above behaving like Inner Join, can someone explain?

The phrase about "query becomes/behaves like Inner join" is not correct in general. It is correct only when the column from right table is used directly in WHERE, and the possibility that the right table row defined by joining condition must not present and hence the according column value can be NULL is not taken into account. Your query uses COALESCE, so it takes into account that the column value may be NULL due to joined row absense, and your query produces needed output.

i.e. in this case ON E.EMPID = B.EMPID AND COALESCE(B.BONUS,0)<=1000, should work right?

No, it will produce the output which differs from one you need. Such query variant will also return the row for 'Thomas' with NULL in BONUS column which differs from the desired output.

You may understand this everything if you'd move the condition from WHERE to the output list. Compare the outputs:

SELECT E.NAME, B.BONUS
       , COALESCE(B.BONUS,0)<=1000 AS criteria_for_where
FROM EMPLOYEE AS E 
LEFT JOIN BONUS AS B ON E.EMPID = B.EMPID 
-- WHERE COALESCE(B.BONUS,0)<=1000

and

SELECT E.NAME, B.BONUS
FROM EMPLOYEE AS E 
LEFT JOIN BONUS AS B ON E.EMPID = B.EMPID and COALESCE(B.BONUS,0)<=1000

Also take into account - additional condition and COALESCE(B.BONUS,0)<=1000 is applied to existing rows of BONUS table only, so COALESCE usage makes no sense in the case, and add 3rd query to the comparing which will do the same like 2nd query:

SELECT E.NAME, B.BONUS
FROM EMPLOYEE AS E 
LEFT JOIN BONUS AS B ON E.EMPID = B.EMPID and B.BONUS<=1000

PS. Your task condition tells: "with a bonus less than 1000", not "less or equal" / "not above". So you must use not <= but <.

PPS. "but I am confused its said that when you use where clause on left table with left Join" - you define left/right tables incorrectly. You must look at the table posession in the query text - so EMPLOYEE is left table and BONUS is right table. When LEFT JOIN is used than this means "take all rows from left table", and RIGHT JOIN means "take all rows from right table" accordingly.