Why Crossjoin is less efficient that union

Question

I have the following query that works fast:

SELECT CUSTOMER FROM (
SELECT CUSTOMER FROM
EMAILS INNER JOIN CUSTOMERS 
ON EMAILS.email=CUSTOMERS.email1
UNION
SELECT CUSTOMER FROM
EMAILS INNER JOIN CUSTOMERS 
ON EMAILS.email=CUSTOMERS.email2)

But cross join is taking forewer:

SELECT DISTINCT CUSTOMER FROM
EMAILS INNER JOIN CUSTOMERS 
ON EMAILS.email=CUSTOMERS.email1
OR EMAILS.email=CUSTOMERS.email2
-- alternative: EMAILS.email in (CUSTOMERS.email1,CUSTOMERS.email2)

I don't think you need `DISTINCT` in first query `Union` will remove the duplicates — Pரதீப், Aug 17 '15 at 16:07
Difficult to guess without an idea of what your table definitions, data volumes, data distributions, indexes, query execution plans, or Oracle version are. — David Aldridge, Aug 17 '15 at 18:06

score 0 · Accepted Answer · edited May 23 '17 at 11:59

Already so many discussion are present about OR vs Union you can check here

Since you are comparing one column with two different column you can also try this

SELECT DISTINCT CUSTOMER 
FROM EMAILS 
INNER JOIN CUSTOMERS 
ON EMAILS.email in (CUSTOMERS.email1,CUSTOMERS.email2)

Also you don't need DISTINCT in first query Union will remove the duplicates

Why Crossjoin is less efficient that union

1 Answers1