Basic tips for writing best SQL Query.

1) It is good to use  columns names in SELECT statement instead of than '*'.

For Example: Write the query as

SELECT id, Name, age, Contact, Address FROM Customers;

Instead of:

SELECT * FROM Customers;

2) Minimize the number of sub query block in your query.

For Example: Write the query as

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




Instead of:

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

3) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter.
  
For Example: Write the query as

SELECT Designation, count(Designation) FROM Employee
WHERE Designation!= 'Manager'  GROUP BY Designation; 

Instead of:

SELECT Designation, count(Designation) FROM Employee GROUP BY
Designation HAVING Designation!= 'Manager';

4) Use operator EXISTS, IN and table joins appropriately in your query.

•  Usually IN has the slowest performance.
•  IN is efficient when most of the filter criteria is in the sub-query.
•  EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select p.Name, P.Price, p.Quantity from product p  where EXISTS (select o.Name, o.Price, o.Quantity from orders o
where o.product_id = p.product_id) 

Instead of:

Select p.Name, P.Price, p.Quantity from product p where product_id IN
(select product_id from order_items)

5) Use EXISTS instead of DISTINCT when using joins which involves tables having 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); 

Instead of:

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

6) Try to use UNION ALL in place 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; 

Instead of:

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

7) Be careful while using conditions in where clause.

For Example: Write the query as

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

Instead of:

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%';


Instead of:

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, '%');




Instead of:

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) 

Instead of:

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'; 

Instead of:

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

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.

For Example: Write the query as

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

Instead of:

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

9) Use of valid Schema for SQL Query.

For Example: Write the query as

SELECT id,Name,salary FROM [dbo].[employee] 

Instead of:

SELECT id,Name,salary FROM employee