ADDBASE Research Paper

STI College
Sta. Cruz, Laguna

Research about “Subqueries”
Advanced Database Systems

Submitted by
Roberto O. Ubatay Jr.

Submitted to
Mr. Rogimer Urriza
I. SQL Subquery
Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value in the database.
Subqueries are an alternate way of returning data from multiple tables.
Subqueries can be used with the following sql statements along with the comparision operators like =, , >=, , ANY (2000, 3000, 4000);

EMPNO SAL
---------- ---------- 7566 2975 7698 2850 7782 2450 7788 3000 7839 5000 7902 3000

-- Transformed to equivalent statement without ANY.
SELECT empno, sal
FROM emp
WHERE sal > 2000 OR sal > 3000 OR sal > 4000;

EMPNO SAL
---------- ---------- 7566 2975 7698 2850 7782 2450 7788 3000 7839 5000 7902 3000
C. Subqueries with ALL
The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, ALL (2000, 3000, 4000);

EMPNO SAL
---------- ---------- 7839 5000

-- Transformed to equivalent statement without ALL.
SELECT empno, sal
FROM emp
WHERE sal > 2000 AND sal > 3000 AND sal > 4000;

EMPNO SAL
---------- ---------- 7839 5000

D. Subqueries with EXISTS
When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

For example: If you