Queries and Sub-Queries

Queries and Sub-Queries

A query is an operation that retrieves data from one or more tables. A query is used to extract data from the database in a format which is readable according to the user's request. For instance, if we have a table named employee, we might issue an SQL statement that returns the employee who is paid the most. This request for usable information of the employees to the database is a typical query that can be performed in a relational database.

A subquery can be taken as a form of a SELECT command that appears inside another SQL statement. It is also referred as Nested query. The SELECT commands containing a subquery are referred to as parent statement. The rows returned by the subquery are used by the parent statement. Subqueries are the SELECT statements that are embedded within another SELECT statement. The results of the inner SELECT statement are used in the outer statement in order to help for determining the contents of the final result. Here, inner to outer means evaluation of the statements from right to left. A subselect can be used in the WHERE and HAVING clauses of an outer SELECT statement. Subqueries can be used with a number of operations.

• IN, NOT IN
• ALL
• SOME, ANY
• EXISTS, NOT EXISTS

The ALL operator may be used with subqueries which produce a single column of numbers. If the subquery is preceded by the keyword ALL, the condition will be TRUE only if it is satisfied by all the values produced by the subquery.

The SOME operator may be used with subqueries which produce a single column of numbers. SOME and ANY can be used interchangeably. If the subquery is preceeded by the keyword SOME, the condition will only be TRUE if it is satisfied by any (one or more) values produced by the subquery.

EXISTS and NOT EXISTS produce a simple TRUE/FALSE result. EXISTS is TRUE if and only if there exists at least one row in the result table returned by the subquery and it is FALSE if the subquery returns an empty result table. NOT EXISTS is the opposite of EXISTS.

(=some)≡ inHowever, (≠some)≠ not in

(≠ all)≡ not in However, (= all)≠ in

Subqueries with the SELECT statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is presented below:

SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])

Example: Find all customers who have both an account and a loan at the Perryridge branch
SELECT distinct customer_name
from borrower, loan
where borrower.loan_number= loan.loan_number and
branch_name= 'Perryridge' and
(branch_name, customer_name)
in (select branch_name, customer_name
from depositor, account
where depositor.account_number=account.account_number)

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhatt acharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.