Whats the Difference Between UNION and OR clause in Oracle SQL

pts.
Tags:
Oracle
SQL
I have query like Select empno,sal from employee where deptno = 10 or dept = 20 and i can also write this query in this ways also Select empno,sal from employee where deptno = 10 UNION Select empno,sal from employee where deptno = 20 Now i wanna to know which is better way between these two and how. Thanks and regards Neeraj Goel

Answer Wiki

Thanks. We'll let you know when a new response is added.

Both actually give the same result, but method 1 with the “or” condition is more straight forward and less select statements, which means less work for the oracle server.

In fact, your select statement can be changed to:
Select empno,sal from employee where deptno in (10, 20)
order by deptno, empno

The order by is worth adding as this allows u to get a listing sorted by your dept code (& u are selecting 10, 20 as your dept), followed by empno

Use SQL*PlusWorksheet to enter the 3 different SQL statements and get the cost path, u can also used TOAD if you are more familiar w this tool. The cost path will then tell u which SQL u shld consider

Sorry, Incorect. Do an explain plan. The “OR” operator should be avoided. It will cause the query to do range scans. The union statement will perform much better. <b>This is true only if an index exists on deptno</b>.

Also, consider using a Union All which will skip the Distinct operation done on the Union’ed set. The order by also adds a performance hit and should only be used if needed.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FerencMantfeld
    OR is a conditional operator, whereas UNION is a SET operator. using UNION, you can combine the results of 2 completely different queries' result sets, whereas with an OR operator, you are referring to a single query. Union also eliminated duplicates and sorts on the natural column order in the absence of an Order by clause. the order by is specified on the top set's columns, or in ANSI notation (order by 1,5,7,2). With UNION ALL, duplicates are nOT eliminated and no sorting is done. Most RDBMS supports UNION and UNION ALL. Oracle supports INTERSET and MINUS set operators too. DB2 supports EXCEPT, but you can get around this issue with a NOT EXISTS correlated subquery in Sql Server or mySQL. hope that helps somewhat. Kind Regards: Ferenc
    0 pointsBadges:
    report
  • Stevewaltz
    Both responses are excellent. Of course, the second responder meant to write INTERSECT.
    0 pointsBadges:
    report
  • Welcome
    Hi to all, It could happen that the two queries related by UNION could be processed by Oracle using two different optimizer plans, due to data distributions a.s.o. If for any of the queries a full table scan will be done, then there is no advantage of separating them in two selects. Otherwise, it may be an advantage. Anyway, at least in the new Oracle versions, the cost-based optimizer is supposed to be wise enough to rewrite alone the OR query into a UNION ALL query. By the way, the older rule-based optimizer did ALWAYS do it, so it is not true that one of the queries causes less server work than the other. Also, the cost-based optimizer could take advantage of stored histograms, so that it will have a good knowledge based on which it could decide to rewrite or not to rewrite the query.
    10 pointsBadges:
    report
  • Kccrosser
    I try to avoid "OR" clauses. In any reasonably complex query, it seems like the query optimizer fails to do a good job when it hits an "OR" clause. If there is an index on the column(s) of interest, I have found that the UNION set operator always performs better than the "OR" clause. If there is no index (and why not???) then the "OR" may perform better, as it may only require one table scan instead of possibly two table scans with the UNION operator. When dealing with large, complex queries where I need to do an "OR" or a "UNION", I often try to encapsulate that in a subquery that returns the primary keys of the records that meet the condition. I.e., instead of: select ... lots of columns ... from ... tables ... where ...conditions... and mycolumn = "X" union select ... lots of columns ... from ... tables ... where ...conditions... and mycolumn = "Y" I will try to do something like: select ... lots of columns ... from ... tables ... where ...conditions... and myPrimaryKeys in ( select myPrimaryKeys from myTable where mycolumn = "X" union select myPrimaryKeys from myTable where mycolumn = "Y" ) This can keep the join and sort to a minimum, and then only retrieve the additional table information from the main tables onces the primary key values have been determined.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following