SQL queries can also be nested, one within another. Suppose one were interested in finding all those
students whose major advisor was not in the Math Department. One way to learn that is to nest one query
regarding faculty and departments inside another regarding students:
SELECT Sname, Dorm
FROM Student
WHERE MajorAdvisorName
IN (
SELECT Fname
FROM Faculty
WHERE Dept != 'Math'
);
This is called a nested query, or subquery. Usually it is possible to use a join instead of a nested query, but
not always. Later we will discuss correlated subqueries, which cannot be translated into join expressions. In this
case, however, here is the same query example executed as a join:
SELECT Sname, Dorm
FROM Student JOIN Faculty
ON MajorAdvisorName = Fname
WHERE Dept != 'Math';
In this case, since there is no ambiguity about which table is being referenced, the column names do not
need to be qualified with a table reference.
When using a subquery, the result columns must all come from the table named in the FROM clause
of the first SELECT clause. Since that is the case in this example, one has the choice of using a join or
a subquery.
We will return to the SELECT statement later to touch on some additional topics, but now we will turn our
attention to the other SQL DML statements.
Pages:
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426