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')
Efficient:
SELECT *
From EMP (Basic Table)
WHERE Empno> 0
And exists (SELECT 'X'
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.
E.g:
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: Efficient)
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 efficient)
SELECT ....
From EMP E
WHERE NOT EXISTS (SELECT '
From DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
And dept_cat = 'a');
