Came across this query online some where, explaining inner join with a condition (incentive>3k).Was just wondering if I can use where clause instead of 'AND' in below SQL, if yes, whats will be the output difference in both?
FROM employee a
INNER JOIN incentives B ON A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
AND INCENTIVE_AMOUNT > 3000;
They come to the same thing.
However, it is generally considered good practice to use
join clauses only for specifying joins, and keep filter predicates in the
where clause, hence:
select first_name, incentive_amount from employee e join incentives i on i.employee_ref_id = e.employee_id where i.incentive_amount > 3000
(Note that the
outer keywords are optional and in my view redundant clutter, so I never use them.)
In the case of outer joins (
left join, or if you absolutely must,
left outer join), the whole idea of separating the filter predicates and placing them in the
where clause goes out of the window, because (for example) if there is no
incentive row then
i.incentive_amount will be null and so any predicate whatsoever will exclude the row. Some say this is why ANSI join syntax is rubbish and the distinction between join and filter predicates is artificial and pointless, while others see it as a quirk of an otherwise helpful syntax.
select first_name, incentive_amount from employee e left join incentives i on i.employee_ref_id = e.employee_id and i.incentive_amount > 3000
You could still follow the convention for inner joins in the same query, e.g:
select first_name, incentive_amount from employee e join departments d on d.department_id = e.department_id left join incentives i on i.employee_ref_id = e.employee_id and i.incentive_amount > 3000 where d.name = 'Philology'
Just to add, I agree with Jonathan Lewis that
b are terrible aliases for
incentives. (By the way, why not
incentive?) In my version I have used the surely more readable