SUB QUERIES
Jump to navigation
Jump to search
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 sales_date, sum(total_amount) AS sales_total FROM sales GROUP BY sales_date) st WHERE s.sales_date = st.sales_date;e
Problem: Select data from sales table where the selected TOTAL_AMOUNT are greater than the average TOTAL_AMOUNT of their respective customer
SELECT * FROM SALES a WHERE TOTAL_AMOUNT >(SELECT AVG(TOTAL_AMOUNT) FROM SALES b WHERE b.customer_id = a.customer_id) ORDER BY CUSTOMER_ID;