Difference between revisions of "SUB QUERIES"

From rbachwiki
Jump to navigation Jump to search
Line 27: Line 27:
  WHERE sales_date = '01-jan-2015'
  WHERE sales_date = '01-jan-2015'
  )
  )
USING subqueries in the FROM clause
Used mostly when we join detailed data with aggregated data
SELECT s.sales_date, s.order_id, s.customer_id, s.salesperson_id, s.total_amount, st.sales_total
FROM sales s, (SELECT sum(total_amount) AS sales_total FROM sales) st


[[#Rules for Character Strings Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]
[[#Rules for Character Strings Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Revision as of 21:48, 14 November 2017

A SUB QUERY is a query within a query which can return one or more rows

SELECT * FROM SALES
WHERE CUSTOMER_ID =
(SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH'

A SUBQUERY executes first before the main query, SO the customer id was returned from the the subquery then passed to the original query

When the subquery returns more than one value use the IN instead of the =

SELECT * FROM SALES
WHERE CUSTOMER_ID IN  
(SELECT CUSTOMER_ID FROM CUSTOMER WHERE REGION ='SOUTH')
SELECT * FROM SALES
WHERE CUSTOMER_ID IN
(SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH' OR last_name = 'mann'

SUBQUERY ON MULTIPLE COLUMNS

SELECT sales_date, order_id, customer_id, product_id, unit_price
FROM sales
WHERE (product_id, unit_price) IN
(
SELECT product_id, unit_price
FROM sales
WHERE sales_date = '01-jan-2015'
)

USING subqueries in the FROM clause

Used mostly when we join detailed data with aggregated data
SELECT s.sales_date, s.order_id, s.customer_id, s.salesperson_id, s.total_amount, st.sales_total
FROM sales s, (SELECT sum(total_amount) AS sales_total FROM sales) st


Back To Top- Home - Category