Satyendra Sharma Satyendra Sharma - 1 month ago 9
MySQL Question

Can I use where clause instead of AND in given SQL query? If yes, whats the difference in both?

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?

SELECT FIRST_NAME,
INCENTIVE_AMOUNT
FROM employee a
INNER JOIN incentives B ON A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID
AND INCENTIVE_AMOUNT > 3000;

Answer Source

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 inner and 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 a and b are terrible aliases for employee and incentives. (By the way, why not employees or incentive?) In my version I have used the surely more readable e and i.