How To Use Subqueries in the FROM clause

Q

How To Use Subqueries in the FROM clause? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements

✍: FYIcenter.com

A

If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. The following statement shows you how to use a subquery as base table for the main query:

SQL> SELECT * FROM (
  2    SELECT first_name, last_name, department_name
  3    FROM employees e, departments d 
  4    WHERE e.department_id = d.department_id
  5  ) WHERE department_name LIKE 'S%' ORDER BY last_name;
FIRST_NAME        LAST_NAME              DEPARTMENT_NAME
----------------- ---------------------- ---------------
Ellen             Abel                   Sales
Sundar            Ande                   Sales
Mozhe             Atkinson               Shipping
Amit              Banda                  Sales
Elizabeth         Bates                  Sales
Sarah             Bell                   Shipping
......

2007-04-19, 4947👍, 0💬

How To Name Query Output Columns
How To Name Query Output Columns? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements Each column in the query output has a default name. If you don't like the default name, you can specify a new name for any column in the query output by using the AS clause. The following statement shows y... 2007-04-19, 5551👍, 0💬

How To Count Groups Returned with the GROUP BY Clause
How To Count Groups Returned with the GROUP BY Clause? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. If you want to count the num... 2007-04-19, 5534👍, 0💬

How To Write a Left Outer Join with the WHERE Clause
How To Write a Left Outer Join with the WHERE Clause? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements If you don't want to use the LEFT OUTER JOIN ... ON clause to write a left outer join, you can use a special criteria in the WHERE clause as "left_table.column = right_table.column(+)".... 2007-04-19, 5490👍, 0💬

What Is a Subquery
What Is a Subquery? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The ou... 2007-04-19, 5162👍, 0💬

How To Use Subqueries with the EXISTS Operator
How To Use Subqueries with the EXISTS Operator? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subq... 2007-04-19, 5145👍, 0💬

How To Start a New Transaction
How To Start a New Transaction? - Oracle DBA FAQ - Understanding SQL Transaction Management There is no SQL statement to explicitly start a new transaction. Oracle server implicitly starts a new transaction with the following two conditions: The first executable statement of a new user session will ... 2007-04-18, 5051👍, 0💬

How To Use Subqueries with the IN Operator
How To Use Subqueries with the IN Operator? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation... 2007-04-19, 5038👍, 0💬

What Is a Transaction
What Is a Transaction? - Oracle DBA FAQ - Understanding SQL Transaction Management A transaction is a logical unit of work requested by a user to be applied to the database objects. Oracle server introduces the transaction concept to allow users to group one or more SQL statements into a single tran... 2007-04-18, 5021👍, 0💬

How To Use Subqueries in the FROM clause
How To Use Subqueries in the FROM clause? - Oracle DBA FAQ - Understanding SQL SELECT Query Statements If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. The following... 2007-04-19, 4948👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.