# SQL Query Optimization Tools | Query Tuning Tips

Job-ready Online Courses: Click, Learn, Succeed, Start Now!

In our last SQL tutorial, we discussed SQL Subqueries. In this session of SQL, we will see SQL Query Optimization or SQL Query Tuning.

Moreover, we will learn different techniques for optimizing or tuning a query in SQL.Â  Also, we will see SQL Query Optimization examples.

So, let us start the SQL Query Optimization tutorial.

### Introduction to SQL Query Optimization

SQL Statements are used to recover data from the database.

We can get the same outcomes by optimizing diverse SQL queries.

In any case, use of the best query is vital when execution is considered.

### SQL Query Optimization/Tuning Techniques

1. The SQL query turns out to be quicker in the case that you use the real segment names in SELECT rather than ‘*’.

For example: Write the query as

`SELECT id, first_name, last_name, age, subject FROM student_details;`

Rather than:

`SELECT * FROM student_details;`

2.Â Having condition is used to channel the rows after every one of the rows is chosen. Try not to use HAVING provision for some other purposes.

For example: Write the query as

```SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;```

```SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';```

3. Sometimes you may have in excess of one subquery in your principle query.

For example: Write the query as

```SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';```

```SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';```

4. Use administrator EXISTS, IN and table joins suitably in your query.

a) Usually IN has the slowest execution.
b) IN is effective when a large portion is in the sub-query.
c) EXISTS is useful.

For example: Write the query as

```Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)```

```Select * from product p
where product_id IN
(select product_id from order_items```

5. Use EXISTS rather than DISTINCT when using joins which includes tables having the one-to-many relationship.

For example: Write the query as

```SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);```

```SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;```

6. Try to use UNION ALL instead of UNION.

For example: Write the query as

```SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;```

```SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;```

7. Be cautious while using conditions in WHERE statement.

`SELECT id, first_name, age FROM student_details WHERE age > 10;`

`SELECT id, first_name, age FROM student_details WHERE age != 10;`

Write the query as

```SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';```

```SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';```

Write the query as

```SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');```

```SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);```

Write the query as

```SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)```

```SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)```

Write the query as

```SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';```

```SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';```

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

```SELECT id, name, salary
FROM employee
WHERE salary < 25000;```

```SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;```

Write the query as

```SELECT id, first_name, age
FROM student_details
WHERE age > 10;```

```SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;```

8. Use DECODE to stay away from the checking of same columns or joining a similar table monotonously. It can likewise be made used instead of GROUP BY or ORDER BY statement.

For example: Write the query as

```SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';```

```SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';```

9. To compose the query which gives proficient execution take after the general SQL standard.

a. Use single case for all SQL verbs.
b. Begin all SQL verbs on another line.
c. Separate all words with a solitary space.
d. Right or left adjusting verbs inside the underlying SQL verb.

So, this was all in SQL Query Optimization. Hope you like our explanation of SQL Tuning.

### Summary

Hence, in this SQL Query Optimization tutorial, we discussed how to optimize a query in SQL.

Moreover, we saw SQL Query Optimization Techniques. Also, we discussed SQL Query Optimization examples.

Still, if you are having any confusion in SQL Query Optimization/Tuning, feel free to ask in the comments.

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google

DataFlair Team

The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.

1. janu says:

baley