Why do these 2 queries return different results from the Oracle hr database?-CodePudding

select J.job_title,
       D.department_name,
       E.first_name || ' ' || E.last_name as "Employee Name",
       H.start_date
from   job_history H
       join jobs J
       on H.job_id = J.job_id
       join departments D
       on H.department_id = D.department_id
       join employees E
       on H.department_id = E.department_id
where  H.start_date between '01-Jan-93' and '03-Aug-97';

2nd query

SELECT job_title,
       department_name,
       first_name || ' ' || last_name AS Employee_name,
       start_date 
FROM   job_history
       JOIN jobs USING (job_id) 
       JOIN departments USING (department_id) 
       JOIN  employees USING (employee_id) 
WHERE  start_date between '01-jan-93' AND '08-aug-97';

CodePudding user response：

You get different answers (more rows from the first query, which is very likely the wrong query) because in the first query you join the last table, employees, to the first table, job_history, on the department_id column. (In the second query you join by employee_id, which is probably the correct way.)

This way, for every row in job_history that matches and employee in the employees table on the department_id column you will get a row in the join output - even if the employee doesn't match that job_history row by employee id. All employees in the Executive department will appear in the output.

Note that your where clause is not the same (one period ends 3 August, the other 8 August), but that is not the reason for the different output. (It might be on different data though.)

Also, they should teach you not to use strings in date comparisons; but this is unrelated to your question.