Recommended users, personally test, use it, do not blindly use the following methods and suggestions! 17. Use the alias (Alias)
When connecting multiple tables in a SQL statement, use the aliasing name and embark on each Column. This way, you can reduce the parsing time and reduce the grammatical errors caused by the ambiguity of Column.
(Translator Note: Column ambiguity is due to different tables in SQL have the same Column name, when this Column appears in the SQL statement, the SQL parser cannot judge the belt of Column
18. Substitution with exists in
In many queries based on the base table, in order to meet a condition, another table is often needed to join. In this case, using Exists (or NOT EXISTS) will usually improve the efficiency of the query.
Inefficient:
Select * from EMP (Basic Table) Where Empno> 0 and Deptno in (Select Deptno from DePT where Loc = 'MELB') is efficient:
SELECT * FROM EMP (Basic Table) Where Empno> 0 and Exists (SELECT '×' from DePt Where Dept.deptno = Emp.Deptno and Loc = 'MELB')
(Translator Press: Relatively, replace Not in IN with NOT EXISTS will more significantly improve efficiency, the next section will be pointed out)
19. Substitution with not exists Not in
In the child query, the Not in clause will execute an internal sort and merge. No matter which case, Not in is minimal (because it performs a full table traversal in the subquery). In order to Avoid using NOT IN, we can rewrite the Outer Join, or NOT EXISTS.
For example: select ... from Emp where dept_no not in (select dept_no from dept where dept_cat = 'a');
In order to improve efficiency. Rewriting is:
(Method 1: High efficiency) Select .... From Emp A, Dept B Where a.dept_no = b.dept ( ) and b.dept_no is null and b.dept_cat ( ) = 'a'
(Method 2: Most Effective) --- can use Select .... From Emp E WHERE NOT EXISTS (SELECT '×' from dept d where d.dept_no = E.DEPT_NO AND DEPT_CAT = 'A');
