Subqueries

Subquery is a query within a query. When a query is dependent on the outcome of another uery, we call it a subquery. Take the two tables, Emp_Add and Emp_skill given below.

Emp_Add

ssn	fname	lname	address	city	state
512687458	Joe	Smith	83 First Street	Howard	Ohio
758420012	Mary	Scott	842 Vine Ave.	Losantiville	Ohio
102254896	Sam	Jones	33 Elm St.	Paris	New York
876512563	Sarah	Ackerman	440 U.S. 110	Upton	Michigan

Emp_skill

ssn	skill	id
512687458	Manager	672
512687458	Software	700
102254896	Hardware	562
758420012	Manager	531

Suppose, we want the names of all those employees that have some skill. The query for this is given below:

SELECT fname, lname
FROM Emp_Add
WHERE ssn=(SELECT DISTINCT ssn FROM Emp_skill);

The result-set will be:

fname	lname
Joe	Smith
Sam	Jones
Mary	Scott

Now, we will see how this query works. The SELECT query in the beginning is known as Outer Query and the query within the parenthesis is known as Inner Query. The output of the outer query is dependent on the output of the inner query. Hence the inner query will be executed first. The inner query is:

SELECT DISTINCT ssn 
FROM Emp_skill;

The result of the above query is:

ssn
512687458
102254896
758420012

Now query will become:

SELECT fname, lname
FROM Emp_Add
WHERE ssn=(512687458, 102254896, 758420012);

It has a become a simple query now and the desired output will be the result-set.