advanced-sql-tutorial

Oracle Consulting and Training

For Better, Faster, Smarter

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 useful for hiding 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

tutorials

.

Part 1 of this advanced SQL tutorial (this tutorial)

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

There are two types of sub query: correlated and non-

correlated. 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.

In this tutorial we’ll look at non-correlated sub queries. See

advanced sql tutorial part 4

correlated sub queries.

Sub queries are also known as nested queries) and are used to answer multi-part questions. They are often

interchangeable with joins and the Oracle optimiser may well treat a sub-query exactly as if it were a join.

Let’s take the trivial example of finding the names of everybody who works in the same department as an

employee called "Jones" to illustrate this.

We can write the SQL for this in 2 ways - as a sub query:

SELECT name FROM emp WHERE dept_no =

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

or as a join by joining the emp table to itself:-

SELECT e1.name

FROM emp e1, emp e2

WHERE e1.dept_no = e2.dept_no

AND e2,name = 'JONES'

With the first example we have to be careful to ensure that the subquery return no more than one row,

otherwise Oracle will generate an exception at run time. To be safe we can change "=" to "IN" as shown in

this next example.

SELECT name FROM emp WHERE dept_no IN

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

for such a simple query like this there would be very little difference in terms of performance of the SQL as

a subquery or as a join, 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 and examining the execution plans

before deciding on a particular approach, unless they're very simple queries.

Learn more about

Oracle performance tuning here

.

Continue this

Advanced SQL tutorial

and learn more about non-correlated sub-queries or see

about correlated sub queries.

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

Looking to sky-rocket productivity, save time and reduce costs?

Training is a highly cost-effective and proven method of boosting productivity.

Smartsoft offers instructor-led

training

in Oracle and related technologies on or off site in cities across the

UK as well as self-study

online training.

See our scheduled

Oracle training courses,

requirements

.

Oracle tips and tricks