oracle
  1. oracle-subquery
PreviousNext

SUBQUERY - (Oracle Misc)

In Oracle database, a subquery is a query that is nested within another query. A subquery is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name expression  (SELECT column_name(s) FROM table_name WHERE condition);

Here, table_name is the name of the table, column_name is the name of the column, expression is the expression used to retrieve the data, and condition is the condition used to filter the data.

Example

Consider the following two tables - "Customer" table and "Order" table:

Customer table
+----+-----------+
| Id | Name      |
+----+-----------+
|  1 | John Doe  |
|  2 | Jane Doe  |
|  3 | Alan Smith|
+----+-----------+

Order table
+----------+------------+-------+
| Order_Id | Order_Date | Amount|
+----------+------------+-------+
|        1 | 2020-01-01 |  1000 |
|        2 | 2020-01-02 |  2500 |
|        3 | 2020-01-03 |  1500 |
|        4 | 2020-01-04 |  3000 |
+----------+------------+-------+

Let's say we want to find out the customers who have placed an order more than $2000.

SELECT Name FROM Customer
WHERE Id IN (SELECT Customer_Id FROM Order WHERE Amount > 2000);

Output

+-----------+
| Name      |
+-----------+
| John Doe  |
| Jane Doe  |
| Alan Smith|
+-----------+

Explanation

In the above example, we have used a subquery to first retrieve the Customer_Id from the Order table, where the Amount of the order is greater than $2000. We have then used this subquery as a condition in the main query to retrieve the Name of the customers who have placed the order.

Use

Subqueries in Oracle are often used to filter data and make more complex queries. They provide a powerful and flexible way to retrieve data that meets specific criteria.

Important Points

  • A subquery is a query nested within another query.
  • It is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved.
  • Subqueries are often used to filter data and make more complex queries.
  • A subquery can return a single value or a set of values.

Summary

In summary, subqueries in Oracle are a powerful and flexible way to retrieve data that meets specific criteria. They enable you to filter data and make more complex queries by nesting one query within another.

OR OperatorTRUNCATE
Published on: