Tips>>SQL

MAKE FULL WEB APPLICATION WITH JUST SQL KNOWLEDGE? CLICK HERE

SQL SUBQUERY

It is very powerful and flexible feature of SQL. Through subqueries, we can embed a query within an update,delete or select statements.Sub Queries are useful where the desired output is based on some query or when we do not have the predefined list of values.

The syntax of the select clause is to retrieve based on output of a subquery :-

SELECT column_name [,column_name...]
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name
where column_name = <value> )

Table Store_Information :-

Store_Name	Sales	Date
The Trend	1500	Jan-05-2006
Su Ru	250	Jan-07-2006
The Trend	300	Jan-08-2006
Positive	700	Jan-08-2006

Table Geography :-

Region_Name	Store_Name
North	Positive
North	Tantra
South	The Trend
South	SU RU

and we want to use a subquery to find the sales of all stores in the South region. To do so, we use the following SQL statement :-

SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'South')

Result :-

SUM(Sales)

2050

In this example, instead of joining the two tables directly and then adding up only the sales amount for stores in the South region, we first use the subquery to find out which stores are in the South region, and then we sum up the sales amount for these.

If you don't find what you are looking for. Please click here to submit your query, our experts will reply soon.

E-mail : sales@virtualsplat.com
Phone : +91-9892413501

Whatsapp Icon +91-9967648641