SQL Nested select in From caluse

Question

I am confuse, can we write SELECT statement in FROM clause and if yes why can it be.

SELECT v.employee_id, v.last_name, v.lev 
   FROM 
      (SELECT employee_id, last_name, LEVEL lev 
      FROM employees v
      START WITH employee_id = 100 
      CONNECT BY PRIOR employee_id = manager_id) v 
   WHERE (v.employee_id, v.lev) IN
      (SELECT employee_id, 2 FROM employees);

What exactly is your question? – a_horse_with_no_name Dec 08 '16 at 07:13 — a_horse_with_no_name, Dec 08 '16 at 07:13
I ask the usage of select in from clause. – Pune Dec 08 '16 at 07:56 — Pune, Dec 08 '16 at 07:56

score 1 · Answer 1 · answered Dec 08 '16 at 07:30

1

I think you are looking for a recursive cte in sql server which would be something like.....

WITH X (employee_id, last_name, lev )
AS (

    SELECT employee_id, last_name, 0 AS lev 
    FROM employees 
    WHERE manager_id IS NULL

    UNION ALL 

    SELECT e.employee_id, e.last_name , lev + 1
    FROM employees e
    INNER JOIN x ON x.employee_id = e.manager_id

   )
SELECT v.employee_id, v.last_name, v.lev 
FROM X
WHERE lev = 2

answered Dec 08 '16 at 07:30

M.Ali

62,698
12
85
116

I think he's using Oracle, at the query certainly looks like it. – Tim Biegeleisen Dec 08 '16 at 07:31
The question was tagged with `SQL Server` unless the OP has changed his mind. – M.Ali Dec 08 '16 at 07:32
I think the OP already has a functioning query and is just asking about usage of the `WHERE` clause. – Tim Biegeleisen Dec 08 '16 at 07:34
sorry all my bros, I ask the use of select in from clause. – Pune Dec 08 '16 at 07:56

score 1 · Answer 2 · edited May 23 '17 at 11:53

1

There can be multiple reasons. Writing any select query in from clause creates inline view.

This is what I can think,

To get top N result from the query.
You may want to restrict data by restricting in from clause itself.

Check out my answer for top n query.

How do I limit the number of rows returned by an Oracle query after ordering?

edited May 23 '17 at 11:53

Community

1
1

answered Dec 08 '16 at 10:45

sandi

86
4

score 1 · Accepted Answer · answered Dec 08 '16 at 10:51

The answer is yes, you can use. The select clause in the from will act as a inline view(consider it as a temporary table that databse creates to hold the results). For example:

    SELECT sdt sdat
      FROM (SELECT SYSDATE sdt FROM dual);

In the above query, SELECT SYSDATE sdt FROM dual is executed first, and output would be like:

    sdt
    ---
    08-Dec-2016 16:20:56

Then, using this as a temp table(which is called an inline view in such cases), oracle will execute your outer select on this data. Hence SELECT sdt FROM... executes, giving the final output as:

    sdat
    ----
    08-Dec-2016 16:20:56

Thank you very much and now i understand that writing **SELECT** statement in **FROM** call **INLINE** view. — Pune, Dec 09 '16 at 09:49

SQL Nested select in From caluse

3 Answers3