Oracle Subqueries

In Oracle, subquery is a query within a query. In other words nested query. The subquery can be creates with WHERE clause, FROM clause or with SELECT clause.

Oracle Subqueries Syntax With WHERE clause

The subquery can be used with the WHERE clause and called nested subqueries, use the following syntax:

Oracle Subqueries example With WHERE clause

The following Oracle example shows that how to use subquery using WHERE clause.

Example

SELECT A.* 
FROM tblCustomerLoan A
WHERE A.firstName IN (SELECT B.firstname
                          FROM tblCustomer B
                          WHERE B.custId = A.custId);

Note:

In the above example, we are retrieving all the records from "tblCustomerLoan" table, if firstName found in the subquery.

Oracle Subqueries example With FROM clause

The following Oracle example shows that how to use subquery using FROM clause.

Example

SELECT A.firstName, A.lastName, subquery1.totalHover
FROM  tblCustomer A, (SELECT SUM(orders.workingHover) AS totalHover
  FROM orders
  GROUP BY firstName) subquery1
WHERE subquery1.custId = A.custId;

Note:

In the above example, subquery has been aliased with the name subquery1 and used to reference for its any fields.

« Previous