Constants and Variables

Constants, program variables etc. should be on the right hand side of  a WHERE  or  HAVING clause.

SELECT    ename
                             FROM      emp
                             WHERE     empno = '1232'

Or

SELECT    ename
                             FROM      emp
                             WHERE     empno = :1

Outer Joins

Columns  requiring to be outer joined  should appear on the right hand side of a WHERE or HAVING clause.

SELECT    ename
                             FROM      emp e, dept d
                             WHERE     e.empno = d.empno(+)    

Table aliases.

Table aliases should be used in all queries that have more than one table in the FROM  clause.

The use of table aliases speeds up the parse phase of  an  oracle query, by reducing the number ofrecursive sql queries.

                                 SELECT    count(*) 
                                 FROM      oe o,
                                           oe_link l,
                                           oe_link_name n
                                 WHERE     o.oe = l.oe
                                 AND       l.name = n.name

Notice the table aliases o,l,n  and their subsequent use in the where clause.

Ordering of where clauses.

As an aid to understanding,  WHERE  clauses should  be laid out with the join clauses first, and the restriction clauses second.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.