Advanced SQL Tutorial

This advanced SQL tutorial focuses on the design of more complex SQL statements and the strategies available for implementing them, it concentrates on sub queries and joins because they are often inter-changeable, and views because these are often used to hide the complexity of queries involving sub-queries and joins.

As this is an advanced tutorial there is some consideration of performance issues, but this aspect is more thoroughly explored in our series on Oracle performance tuning.

You might also be interested in our beginner's SQL tutorial, our PL/SQL tutorials and our Oracle tutorials

Part 1 of this advanced SQL tutorial (this article) introduces sub-queries and looks at non-correlated sub-queries.Part 2 looks at the use of sub-queries.

Part 3 looks at nested sub queries, sub-queries in the from clause and sub-queries that return no rows. Part 4 of this tutorial covers correlated sub-queries.

Sub Queries

Sub queries are also known as nested queries and are used to answer multi-part questions. Sub queries and joins are often interchangeable and in fact the Oracle optimiser may well treat a query containing a sub-query exactly as if it were a join.

Let's use a trivial example of finding the names of everybody who works in the same department as a person called Jones to illustrate this point. The SQL could be written using a sub query as follows:

SELECT name FROM emp WHERE dept_no =
(SELECT dept_no FROM emp WHERE name = 'JONES')

or as a join statement, like this:-

SELECT e1.name FROM emp e1,emp e2
WHERE e1.dept_no = e2.dept_no AND e2name = 'JONES'

With a trivial example like this there would probably be very little difference in terms of performance of the SQL for such a simple query, but with more complex queries there could well be performance implications. For this reason it is always worth trying a few variations of the SQL for a query and examining the execution plans before deciding on a particular approach, unless they're very simple queries.

Learn more about Oracle performance tuning here.

Non Correlated Sub-Queries

There are, in fact, two types of sub query: correlated and non-correlated. The example shown above is a non-correlated sub query. The difference between them is that a correlated sub query refers to a column from a table in the parent query, whereas a non-correlated sub query doesn't. This means that a non-correlated sub query is executed just once for the whole SQL statement, whereas correlated sub queries are executed once per row in the parent query.

See advanced SQL tutorial (part 4) for more on correlated sub queries.

Click on the link to continue this Advanced SQL tutorial

---------------------------------------

For more Oracle Tips and Tricks sign up to our ezine. There's no frills, no fluff, just solid, reliable technical information. Take a short cut to your success and subscribe now. It's jam-packed full of tips and tricks to help you make your Oracle applications better, faster and smarter. Sign up today and your first issue will soon be winging its way to your mailbox.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.