SQL tuning skills

SQL tuning skills

-- 1 if you use the actual column name instead of * begin ---
Select ID, first_name, last_name, age, subject from student_details;

Replace

Select * From student_details;

-- 1 if you use the actual column name instead of * end ---

-- 2 having statement is used to filter rows after all rows are selected, that is, used in combination with group. Do not use begin for other purposes ---
Select subject, count (subject)
From student_details
Where subject! = 'Science'
And subject! = 'Maths'
Group by subject;

Replace:

Select subject, count (subject)
From student_details
Group by subject
Having subject! = 'Vancouver 'and subject! = 'Toronto ';

-- 2 end ---

-- 3 there may be more than one subquery in your primary query. Please try to minimize the number of subquery blocks begin ---
Select name
From employee
Where (salary, age) = (select max (salary), max (AGE)
From employee_details)
And dept = 'electronics ';

Replace two subqueries with one subquery:

Select name
From employee
Where salary = (select max (salary) from employee_details)
And age = (select max (AGE) from employee_details)
And emp_dept = 'electronics ';

-- 3 end ---

-- 4 exists, in, tablejoin should use the appropriate begin ---
1 In has the lowest performance
2. Most Filters criteria are efficient in subqueries.
3. When Most Filters criteria are in the main query, exist is efficient.

Select * from product P
Where exists (select * From order_items o
Where o. product_id = P. product_id)

Instead:
Select * from product P
Where in (select * From order_items o
Where o. product_id = P. product_id)
-- 4 end ---

-- 5 Use exists instead of distinct. When the tables included in the join operation have one to many relations begin ---
Select D. dept_id, D. Dept
From dept d
Where exists (select 'x' from employee e where E. Dept = D. Dept );

Replace:
 
Select distinct D. dept_id, D. Dept
From dept D, employee e
Where E. Dept = E. Dept;

-- 5 end ---

-- 6 replace Union begin with unique all ---
Select ID, first_name
From student_details_class10
Union all
Select ID, first_name
From sports_team;

Instead:
 
Select ID, first_name, Subject
From student_details_class10
Union
Select ID, first_name
From sports_team;

-- 6 end ---

-- 7 be careful when using condition begin where clause ---
Select ID, first_name, age from student_details where age> 10;

Instead:
 
Select ID, first_name, age from student_details where age! = 10;
---
Select ID, first_name, age
From student_details
Where first_name like 'Chan % ';
 

Instead:
 
Select ID, first_name, age
From student_details
Where substr (first_name, 1, 3) = 'cha ';

---
Select product_id, product_name
From Product
Where unit_price between Max (unit_price) and min (unit_price)

Instead:
 
Select product_id, product_name
From Product
Where unit_price> = max (unit_price)
And unit_price <= min (unit_price)
---
Do not use non-column expression on the query side because it will be processed early.
Select ID, name, salary
From employee
Where salary <1, 25000;

Instead:
 
Select ID, name, salary
From employee
Where salary + 10000 <35000;
---
Select ID, first_name, age
From student_details
Where age> 10;

Instead:
 
Select ID, first_name, age
From student_details
Where age not = 10;

-- 7 end ---