0

Can someone elaborate on how the following two queries would provide different results?

That is, how the query is processed differently using WHERE or AND.

select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
where
    accounts.sales_rep_id = 321500;
select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
    and accounts.sales_rep_id = 321500;
  • 1
    I suggest you create a minimal example in a dbfiddle or similar. If it is something in particular that you dont understand, post the link to the fiddle – Lennart Jul 27 '19 at 9:15
3

As Lennart has suggested, use a minimal example and see what happens.

create table accounts(account_id int primary key, sales_rep_id int);

create table orders(order_id int primary key, account_id int);

insert into accounts values
(1, 321500),
(2, null),
(3, 2000),
(4, 3000);

insert into orders values
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2),
(6, 3);

There is a LEFT JOIN involved, that means it returns always all rows of orders table, plus those rows of accounts table with the same account_id. But, the WHERE clause filters final result to those with sales_rep_id = 321500.

order_id | account_id | account_id | sales_rep_id
-------: | ---------: | ---------: | -----------:
       1 |          1 |          1 |       321500
       2 |          1 |          1 |       321500

However the second query returns all rows of orders table, but only accounts with the same account_id and sales_rep_id = 321500.

order_id | account_id | account_id | sales_rep_id
-------: | ---------: | ---------: | -----------:
       1 |          1 |          1 |       321500
       2 |          1 |          1 |       321500
       3 |          2 |       null |         null
       4 |          2 |       null |         null
       5 |          2 |       null |         null
       6 |          3 |       null |         null 

db<>fiddle here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.