Using Subqueries to Solve Queries

• Who has a sal greater than Scott’s ?

• 

Subquery Syntax :

• SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

• The subquery (inner query) executes once before the main query (outer query)
• The result of the subquery is used by the main query

Using a Subquery :

• SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘Scott');

Guidelines for Using Subqueries :

1. Enclose subqueries in parentheses
2. Place subqueries on the right side of the comparison condition
3. The ORDER BY clause in the Subquery is not needed unless you are performing Top-N analysis
4. Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries

Types of Subqueries :

• Single-row subquery

• Multiple-row subquery

INNER Versus OUTER Joins

• In SQL the join of two tables returning only matched rows is called an inner join
• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join
• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join

LEFT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

RIGHT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

FULL OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno) ;

•