How Does Subquery in Select Statement Work in Oracle

How does Subquery in select statement work in oracle

It's simple-

SELECT empname,
       empid,
       (SELECT COUNT (profileid)
          FROM profile
         WHERE profile.empid = employee.empid)
           AS number_of_profiles
  FROM employee;

It is even simpler when you use a table join like this:

  SELECT e.empname, e.empid, COUNT (p.profileid) AS number_of_profiles
    FROM employee e LEFT JOIN profile p ON e.empid = p.empid
GROUP BY e.empname, e.empid;

---

Explanation for the subquery:

Essentially, a subquery in a select gets a scalar value and passes it to the main query. A subquery in select is not allowed to pass more than one row and more than one column, which is a restriction. Here, we are passing a count to the main query, which, as we know, would always be only a number- a scalar value. If a value is not found, the subquery returns null to the main query. Moreover, a subquery can access columns from the from clause of the main query, as shown in my query where employee.empid is passed from the outer query to the inner query.

---

Edit:

When you use a subquery in a select clause, Oracle essentially treats it as a left join (you can see this in the explain plan for your query), with the cardinality of the rows being just one on the right for every row in the left.

---

Explanation for the left join

A left join is very handy, especially when you want to replace the select subquery due to its restrictions. There are no restrictions here on the number of rows of the tables in either side of the LEFT JOIN keyword.

For more information read Oracle Docs on subqueries and left join or left outer join.

Oracle select column as Sub query with case statement

You don't really need a subselect for that. The following OUTER JOIN will output your desired result.

select master.*,
case
    when abc.id is null then '/R'
    when abc.flag = 'U' then '/U'
    else null
end flag
from master
left outer join abc on (master.id = abc.id and master.slsid = abc.slsid)

Note that MASTER is a reserved word in Oracle and you probably should not use it as a table name.

Using a subquery for IN clause Oracle

Use JOIN instead,

and keep an index defined on table1.col1 or table2.col3 or table1.col3 or table3.col :

SELECT col1, col2, col3, ...
  FROM table1 t1
 INNER JOIN table2 t2 on ( t2.col3 = t1.col3 )
 INNER JOIN table3 t3 on ( t1.col1 = t3.col )
 WHERE t1.col2 < 50;

Oracle syntax for subselect in FROM clause?

In the documentation you linked, you can see that table_reference can be a query_table_expression which can be a ( subquery ).

Oracle select with subquery

Try this:

SELECT "NEWS"."NEWSID" as ID,
   "NEWS"."SLUG",
   "NEWS_TRANSLATION".*, 
   (SELECT * FROM (SELECT FILENAME FROM NEWS_MEDIA WHERE NEWSID = ID ORDER BY POSITION ASC) WHERE rownum = 1) as FILENAME
FROM "NEWS" 
INNER JOIN "NEWS_TRANSLATION" ON NEWS.NEWSID = NEWS_TRANSLATION.NEWSID 
WHERE (NEWS.PUBLISH = 1) AND (NEWS_TRANSLATION.LANG = :lang) 
ORDER BY "NEWS"."NEWSID" DESC;

When are you are using "order by" and "rownum" together, you need to first order them and look for the first record.

Oracle SQL - Subquery Works fine, However Create Table with that subquery appears to hang

So the answer to this one.

CREATE TABLE <Table Name> AS 
(
   SELECT foo 
   FROM baa
   LEFT JOIN 
   ( SELECT foo FROM baa WHERE DATES BETWEEN SYSDATE AND SYSDATE - 100 )
   WHERE DATES_1 BETWEEN SYSDATE - 10 AND SYSDATE - 100
)  

The problem was that the BETWEEN statements did not match the same time period and the sub query was looking at more data than the main query (I guess this was causing a full scan over the tables?)

The below query has the matching between statement time period and this returned the results in less than 3 minutes.

CREATE TABLE <Table Name> AS 
(
   SELECT foo FROM baa
   LEFT JOIN ( SELECT foo FROM baa WHERE DATES BETWEEN SYSDATE - 10 AND SYSDATE - 100 )
   WHERE DATES_1 BETWEEN SYSDATE - 10 AND SYSDATE - 100
)  

---

Related Topics