SQL OPTIMIZATION

OPTIMIZATION

In Subquery

Some of us will use a subquery within the IN operator such as this.

SELECT * FROM TABLENAME 

WHERE COLUMN IN

(SELECT COLUMN FROM TABLENAME)

Doing this is very expensive because SQL query will evaluate the outer query first before proceed with the inner query. Instead we can use this instead.

SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable

WHERE dummytable.COLUMN = TABLE.COLUMN;

SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable

WHERE dummytable.COLUMN = TABLE.COLUMN;\

     Using dummy table is better than using an IN operator to do a subquery. Alternative, an exist operator is also better.