Selection of Records from Query Result

Queries in SQL

Operations with Query

Subqueries

SQL allows a query phrase to be a part of another general expression or a generalized table. This query phrase is simply called a subquery. A subquery must be enclosed in parenthesis. A subquery may be a complete query phrase including other nested subqueries. The LIMIT clause may be used in subqueries (usually together with ORDER BY).

Subqueries can be divided according to various criteria. There are three types based on the number of rows and columns:

• A subquery result that has only one row and only one column. The result is the expression in the SELECT part or in the expression in a condition (WHERE condition).

Example 1:

Select companies that have invoices worth more than 10000 (subquery as a condition).

SELECT A.name
FROM Companies A
WHERE 10000 < (SELECT SUM(B.amount)
              FROM INVOICES B
              WHERE A.number=B.firma)

Example 2:

Select invoices that are worth more than the average (subquery as a condition).

SELECT *
FROM Invoices
WHERE amount > (SELECT AVG(amount) FROM Invoices)

Example 3:

Select companies that have at least one invoice worth more than 5000 (subquery as a expression).

SELECT DISTINCT (SELECT name
                 FROM Companies
                 WHERE Companies.number=Invoices.firma) AS name
FROM Invoices
WHERE amount > 5000

• A subquery that has zero or multiple rows and one column (a scalar subquery). Special predicates are used for comparison (IN, ALL, or ANY).

Example 4:

Select the invoices from companies located in Prague (subquery in a condition).

SELECT Invoices.number,INVOICES.company
FROM Invoices
WHERE firma IN (SELECT number
                FROM Companies
                WHERE city LIKE 'Prague%')

Example 5:

Select employees that have a higher salary than anyone in Prague (subquery in a condition).

SELECT TAB1.name,TAB1.salary
FROM People TAB1
WHERE salary > ALL (SELECT salary
                  FROM People 
                  WHERE address LIKE 'Prague%')

• A subquery that has zero or multiple rows and one or multiple columns. Special predicates EXISTS and UNIQUE are used in condition comparison. A subquery may also replace a table in the FROM part.

Example 6:

Select companies that have no invoices (subquery in a condition).

SELECT name
FROM Companies F
WHERE NOT EXISTS (SELECT * FROM Invoices A
                  WHERE A.name=F.name)

Example 7:

Select companies that have Invoices worth more than 10000 (subquery in FROM).

SELECT name, sum
FROM Companies A JOIN (SELECT firma,SUM(amount) AS sum
                   FROM Invoices B
                   GROUP BY B.firma)
           ON (A.number=B.firma)
WHERE sum > 10000 

A subquery may be used in a query phrase located in the SELECT, FROM and WHERE parts. The usage of subqueries is not restricted to query phrases only. They can be used in many other constructions, for example:

IF EXISTS (SELECT admin_enabled FROM System 
           WHERE (admin_name=usr)AND(admin_password=pwd)AND(admin_enabled IS TRUE)) 
  THEN ...

Selection of Records from Query Result

Queries in SQL

Operations with Query