• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

example of nested queries i.e Sub Queries

Can someone give me an example of a nested query in sql server? I typically use inner joins, however i want to expand my knowledge of sub-queries.
0
brgdotnet
Asked:
brgdotnet
2 Solutions
 
elevationkevinCommented:
http://technet.microsoft.com/en-us/library/cc917631.aspx


Oracle/PLSQL: Subqueries

What is a subquery?

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

select * from all_tables tabs
where tabs.table_name in      (select cols.table_name
 from all_tab_columns cols
 where cols.column_name = 'SUPPLIER_ID');
Limitations:

Oracle allows up to 255 levels of subqueries in the WHERE clause.

FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

select suppliers.name, subquery1.total_amt
from suppliers,
   (select supplier_id, Sum(orders.amount) as total_amt
   from orders
   group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;

In this example, we've created a subquery in the FROM clause as follows:

(select supplier_id, Sum(orders.amount) as total_amt
 from orders
 group by supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

Limitations:

Oracle allows an unlimited number of subqueries in the FROM clause.

SELECT clause

A subquery can also be found in the SELECT clause.

For example:

select tbls.owner, tbls.table_name,
  (select count(column_name) as total_columns
   from all_tab_columns cols
   where cols.owner = tbls.owner
   and cols.table_name = tbls.table_name) subquery2
from all_tables tbls;

In this example, we've created a subquery in the SELECT clause as follows:

(select count(column_name) as total_columns
 from all_tab_columns cols
 where cols.owner = tbls.owner
 and cols.table_name = tbls.table_name) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.
0
 
lwadwellCommented:
Using the definition of a nested sub-query being a SELECT within another query, typically a SELECT but could be an UPDATE or DELETE too.

In-line view:
SELECT *
FROM tablea
INNER JOIN (SELECT col1, sum(col2) as total FROM tableb group by col1)v ON tablea.key = v.col1

IN sub-query
SELECT *
FROM tablea
WHERE col1 IN (SELECT col1 FROM tableb WHERE col2 = 'xyz')

Correlated IN sub-query (Correlated: this is where columns in the main query are used inside the sub-query)
SELECT *
FROM tablea
WHERE col1 IN (SELECT tableb.col1 FROM tableb WHERE tableb.col2 = tablea.colx)

Correlated EXISTS sub-query
SELECT *
FROM tablea
WHERE EXISTS (SELECT 1 FROM tableb WHERE tableb.col2 = tablea.colx)
0
 
brgdotnetAuthor Commented:
Thanks guys!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now