Use Subqueries with the IN Operator in Oracle

Q

How To Use Subqueries with the IN Operator in Oracle?

✍: FYIcenter.com

A

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. The following tutorial exercise shows you how to use a subquery with the IN operator:

SQL> SELECT first_name, last_name FROM employees 
  2  WHERE department_id IN (
  3    SELECT department_id FROM departments 
  4    WHERE location_id = 1700
  5  );

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Neena                Kochhar
Lex                  De Haan
Nancy                Greenberg
Daniel               Faviet
John                 Chen
Ismael               Sciarra
......

⇒ Use Subqueries with the EXISTS Operator in Oracle

⇐ What Is a Subquery in Oracle

⇑ Understanding SQL SELECT Query Statements in Oracle

⇑⇑ Oracle Database Tutorials

2019-09-27, 1497🔥, 0💬

Return Top 5 Rows in Oracle
How To Return Top 5 Rows in Oracle? If you want the query to return only the first 5 rows, you can use the pseudo column called ROWNUM in the WHERE clause. ROWNUM contains the row number of each returning row from the query. The following statement returns the first 5 rows from the employees table: ... 2019-09-16, 1915🔥, 0💬

What Is a Subquery in Oracle
What Is a Subquery in Oracle? 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 output of the subquery will be used in the final eval... 2019-09-27, 1811🔥, 0💬

Name Query Output Columns in Oracle
How To Name Query Output Columns in Oracle? 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 you a good example: SQL> SELECT department_id... 2019-09-27, 1764🔥, 2💬

💬 2019-09-19 Aninda mahadev: Good Tutorial!!!

Inner Join with the WHERE Clause in Oracle
How To Write an Inner Join with the WHERE Clause in Oracle? If you don't want to use the INNER JOIN ... ON clause to write an inner join, you can put the join condition in the WHERE clause as shown in the following query example: SQL> SELECT d.department_name, e.first_name, e.last_name 2 FROM... 2019-10-18, 1620🔥, 0💬

Query with a Full Outer Join in Oracle
How To Write a Query with a Full Outer Join in Oracle? If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: departments and employees. The join conditi... 2019-10-18, 1609🔥, 0💬

Use Subqueries with the EXISTS Operator in Oracle
How To Use Subqueries with the EXISTS Operator in Oracle? 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 (subquery)". It returns rows from employees table that t... 2019-09-27, 1590🔥, 0💬

Counting Groups Returned with the GROUP BY Clause in Oracle
How To Count Groups Returned with the GROUP BY Clause in Oracle? 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 number of groups, you can put the GROUP BY query into ... 2019-09-16, 1583🔥, 0💬

Left Outer Join with the WHERE Clause in Oracle
How To Write a Left Outer Join with the WHERE Clause in Oracle? 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(+)". The select statement below is an example of a left... 2019-10-18, 1515🔥, 0💬

Use Subqueries in the FROM Clause in Oracle
How To Use Subqueries in the FROM Clause in Oracle? 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 ... 2019-09-27, 1513🔥, 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.