Advanced SQL Tutorial (ctd)

Part 1 of our Advanced SQL Tutorial introduced sub-queries and looked at non-correlated sub-queries. This part looks at the uses of sub queries and touches on performance issues, but this aspect is more thoroughly explored in our series on Oracle performance tuning. You might also find our beginner's SQL tutorial, our PL/SQL tutorials or our other Oracle tutorials  to be of interest.

Uses of Sub

Queries

The most common use of sub queries is in the WHERE clause of queries to define the limiting condition for the rows returned (i.e. what value(s) the rows must have to be of interest), as in the examples in part 1. However, sub queries can also be used in other parts of the query. Specifically, sub queries can be used: • to define the limiting conditions for UPDATE and DELETE  statements in the following clauses:-           o WHERE           o HAVING           o START WITH • Instead of a table name in           o INSERT statements - ¦           o UPDATE statements - |-- where it defines the columns affected           o DELETE statements - ¦           o the FROM clause of SELECT statements (where it acts like a view) • To define the set of rows to be created in the target table of a CREATE TABLE AS or INSERT INTO sql statement. • To define the set of rows to be included by a view or a snapshot in a CREATE VIEW or CREATE SNAPSHOT statement. • To provide the new values for the specified columns in an UPDATE  statement The first example of sub query shown in advanced SQL tutorial part 1, used a simple equality expression as we were interested in only one row,: SELECT name FROM emp WHERE dept_no = (SELECT dept_no FROM emp WHERE name = 'JONES') but we can also use the sub query to provide a set of rows. For example, to find the names of all employees in the same departments as Smith and Jones, we could use the following SQL statement :- SELECT name FROM emp WHERE dept_no IN (SELECT dept_no FROM emp WHERE name IN ('JONES' ,'SMITH')) In fact, the original example could also return more than one row from the sub query if there were two or more employees called Jones. In the first example a run-time SQL error would be generated in that case, because it specified (by using '=') that the sub query should produce no more than one row (it is perfectly legitimate for a sub query to return no rows). We can reverse the question to ask for the names of all the employees that are NOT in the same department as Jones, To do this, the sense of the sub query just has to be reversed by prefixing it with 'NOT' or '!'. Again depending on whether there might be more than one Jones, we would either use 'IN' or '='. SELECT name FROM emp WHERE dept_no NOT IN (SELECT dept_no FROM emp WHERE name = 'JONES') Or SELECT name FROM emp WHERE dept_no != (SELECT dept_no FROM emp WHERE name = 'JONES') Return to advanced sql tutorial part 1-subqueries                                Continue to part 3 - nested 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, or let us know your requirements. Want a short cut to learning SQL? Just subscribe to our newsletter. 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  Need help with your Oracle systems? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd. All rights reserved.