CODEWITHSUNDEEP
mysqlsqljoinleft-joinon-clause
Jenz
Jenz

Reputation: 8367

MYSQL adding OR clause in left join slowing up the query

I have two database tables: orders and customers.

I'm running a SQL to get all orders in June month.

If the Ship To and Bill To email are different, we are inserting two different records with both emails to customers table.

select o.order_id
     , o.total
     , c.customer_email 
  from orders o 
  left 
  join customers c
    ON o.bill_email = c.customer_email
    OR o.ship_email = c.customer_email
 where DATE(o.order_date) >= '2020-06-01'

But this SQL is taking too much time to load because of the condition, 

ON o.bill_email=c.customer_email 
OR o.ship_email=c.customer_email

How can I add both conditions in ON clause?

Any help would be appreciated.

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use two left joins and put the results in separate columns rather than rows:

select o.order_id, o.total, cb.customer_email, so.customer_email
from orders o left join
     customers cb
     on o.bill_email = cb.customer_email left join
     customers cs
     o.ship_email = cs.customer_email
where o.order_date >= '2020-06-01';

Note that the date() function is not needed.

That said, this seems more easily expressed as:

select o.order_id, o.total, o.bill_email
from orders o 
where o.order_date >= '2020-06-01'
union all
select o.order_id, o.total, o.ship_email
from orders o 
where o.order_date >= '2020-06-01' and s.ship_email <> o.bill_email;

Upvotes: 0

Related Questions