Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 2: SQL Operators
Understanding Sub-Queries:
Query inside another query is called a Sub-Query. Sub-Queries are mostly
used in the WHERE clause but you can use them in the FROM or even in the
SELECT clause. The important thing is to understand the technique and
how to interpret such queries or in other words how Oracle instance will
interpret such queries.
Figure 5: Sub-Queries: Query inside another query.
________________________________________
The technique is, start from the bottom query first or the one within parenthesis, imagine what’s its output will be, replace that query with its imagined output and move to the next higher one. Keep on doing this unless you end up with the main query.
This concept will get further clarified once we start looking at the
following real world examples.
Example:
SELECT *
FROM emp
WHERE sal IN (SELECT sal
FROM emp
WHERE deptno = 30);
Based of the technique that we have defined, we will first imagine the
output of the bottom most query. It will give us all the salary values
from department number 30. Now replace this query with those imagined
values.
Even if multiple values appear as a result of bottom query we can still
use them with IN operator. But the following query will result in an
error if the bottom SELECT statement results in multiple values.
Example:
SELECT *
FROM emp
WHERE sal = (SELECT sal
FROM emp
WHERE deptno = 30);
So be careful when you use operators with sub-queries. The syntax of the
function, clause or Operator should justify under all circumstances then
you are allowed to use sub-queries wherever you like.
Chapter 1: SQL Operators
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: