Using SQL OR and UNION operators

Posted: February 6, 2014 in Database
Tags: , , , , ,

Whenever we write an sql , it is very important that we check the cost of it. If there is any room for improvement in the query based on the time taken by the query to fetch the result.

One such occasion is using an OR clause. There are certain situations where we need “Either this or that” kind of results . We are forced to use OR conditions on that query . But, i personally feel OR condition are little slower than UNION when we hit on the bigger tables .

The UNION query looks bigger , but it actually runs faster than the OR condition. Just for the clarity , am just adding a simple example where we need to find out the employee names and their salary if they have an income greater than 2000 or their past increment is higher than 200

we have the table structures given below

table name : emp
empid empname
1      alice
2      bob
3      david
4      charlie
table name : emp_sal
empid  salary  increment
1      1000     100
2      2000     200
3      4000     400
4      5000     500

by using or :

select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and (s.salary > 2000 or s.increment > 200);

by using UNION :

select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and s.salary > 2000
UNION
select e.empname, s.salary from emp e, emp_sal s where e.empid = s.emp_id and  s.increment > 200;

The union query looks bigger. But , it runs pretty faster than OR query .

Advertisements

Comments are closed.