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.