A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.
Example:
----Example of Correlated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)
GO
A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.
Example:
----Example of Noncorrelated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE c.Title = 'Mr.'
)
GO
Both of above subqueries can be written using Joins, Exists, In clauses. However, I have tried to demonstrate two different kind of subquery examples. Let me know if you have any questions or wants me to elaborate on this subject.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Sir,
I saw your website its really super it very help full to beginners and improve our knowledge this website because it can easily to understand them.
I have lots of doutes in the sql server pls send to my mail -id daily some tips and tricks in sql server sir.
Thanks,
T.Manikumar
I am kaviarasu.I am learning Oracle DBA and i am going to write the certification.Could you sed me the Oracle SQL questions to the above e-mail id.
Hello Dave,
Your site is really nice.can u send me those queries which generally asked in Interviews.I really need it.and more question What is the difference between rowLock and NO Lock.
Thanks and Regards
Aavesh Agarwal
Hi Aavesh,
rowlock is Record wise lock on data.
NoLock is for Object which will not lock any records & whick is not applicable to DML commands like insert, update & Delete commands
Thanks,
Chanti
Good One….
As per books available in the market,it says
DELETE table can be ROLLBACK while
TRUNCATE table CANNOT be ROLLBACK,
But it is paractically wrong,Both can be roll back…
Then why this is so……………
Which books say so?
Truncate can be rolled back if transaction is used
Using both DELETE and TRUNCATE commands can be rolled back.
By default SQL Server commits transactions disabling Rollback.
There are two approaches to rollback your records in case of any mishap or accidently having a record(s)) deleted.
Approach 1:
BEGIN TRANSACTION
…your statements…
COMMIT
Approach 2: SET IMPLICIT_TRANSACTIONS ON
Try this as a test:
Step 1: Approach 2
Step 2: Assuming you have a table ‘tblTableName’ containing rows.
Step 3:
1. DELETE FROM where Code = ”
ROLLBACK
Note: You will see deleted records back into your table.
2. TRUNACATE TABLE
ROLLBACK
Note: You will see deleted records back into your table. Unless you apply COMMIT after each statement that will not then roll back.
Major Difference to remember:
DELETE (DML command): Acts on single or multiple rows. Does not deallocate data page space that contains data.
TRUNCATE (DDL command): Acts on table object, locks it and then purges all rows. Allocates data page space. This is for use only when you need to empty the whole table.
Hope this difference helps.
Just a correction with statement, I used redirectional operators that got omitted. :-D
Here is my test example;
Step 3:
1. DELETE FROM tblEmpMaster where Code = ’01′
ROLLBACK
Note: You will see deleted records back into your table.
2. TRUNCATE TABLE tblEmpMaster
ROLLBACK
[...] A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used. (Read More Here) [...]
anyone who sees this site should improve his knowledge on sql fine work and it should be praised!
i want some full material about SQLSERVER 2005
i can give dude, send ur id and phone no
hi brother…. i am studying sql. Can i get solved subqueries tutorial.
Good article…Dave
keep posting
Regards,
Chintan, Bangalore
hello pinal,
Wish you all happiness. I too read your articles, It is very good.
pinal please give me the solution of this question.
suppose we have a table and two columns ……..
Now the question is we have to interchange the values of
this columns using single query.
please suggest the solution ,
thanks and regards
pawan
Hello Pawan,
The values of two columns can be swapped using simple update statement like:
UPDATE tblTable
SET col1 = col2,
col2 = col1
Regards,
Pinal Dave
Good one, you are making life simpler for lots of them out here. congrats.
Actually if you can post something on order of joins to be followed when working with ..say 5 to 10 tables, it will be great. as the joins increase then how should be order of table to be appearing in join.
I’ve understood that you make the most limiting joins first. Then proceed with all the others.
But simply test it! Write your query in different ways and measure how long they take. Check also the Execution Plan in order to find out if you need to add indexes.
But I must confess that I don’t always practice what I preach. I’m currently dealing with queries where I have to make many OUTER and INNER JOINs, many subqueries, groupings and calculations. I just write good enough query and if it’s too slow it is caught in the tests and it’s sent back to me, and then I try to optimize it.
I’m getting lazy :(
Hi Dave,
I need to have something like this:
SELECT
t1.col_x,
t1.col_y,
(SELECT value FROM t1 WHERE … AND desc = ’12h’) AS ‘ 24h’
FROM (
…
…
…
) AS t1 RIGHT JOIN ( … ) AS t2 ON( .. = .. )
This returns me the error:
Invalid object name ‘t1′.
I saw that to use a subquery I can’t use Temporary Tables. Is there any way to achieve this result? I’m using SQL2005, and I find out that I could use ‘PIVOT and UNPIVOT’ but I haven’t tried yet.
Thanks in advance,
Nuno Costa
hiiiiiiii ,
sir /madam i saw yr website . It’s super & very nice …………….i m doing Sql Server 2005 ……….i have some doubts if u send me some tips about sql server 2005 in my e mail id ……….i will be very happy………..plz sir help me ………
thanks……….
regards
amar
I am abhishek bajpai. I am learning sql dba from NIIT Institute. Please send me some question about sql in my email address.
i m priya . im learing sql server 2005 from niit institute. please send me some question about sql server 2005 in my mail address.
having the knowledge of stored procedure,index,sqbquries,
cursor,view and trigger are sufficent for the student level.
Questions :
finding 2 highest salary
using top variable and row_number functions
inline quries
dynamic quries etc.
Hi,
Could u pls tell us at what scenario we have to use Correlated Subquery? Cos as you told, we can replace this Correlated Subquery by using In, Join clauses!
I meant, what is the need and advantages of this?
Thanks,
Sam.
Really this article is good and try to give more elaborated article on subqueries
You are still my hero, thnx.
Hi Dave,
I need to link two tables without duplication. Suppose if the table A & B are.
(A) (B)
Aid Aname Bid Bname
1 ravi 1 sundar
1 ram
1 raj
The result should be,
Aid Aname Bname
1 ravi Sundar
1 ram
1 raj
Is it possible to get the result by Joins without Temp table?
Pls. help me to sort out the issue. if it possible pls send me to my email.
Thanks in advance.
select t1.Aid,t1.Aname,t2.Bname from A as t1 left join B as t2 on t1.Aid=t2.Aid
hi
can u tell me how can i write query for multiplication cumulative?
example input values
id month value
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
month(1)* value (1)=1
month (2)* previous result(1)=2
month (3)* previous result(2)=6
month (4)* previous result(6)=24
expected output
month multiplied cumulative
1 1
2 2
3 6
4 24
5 120 like this
@muthu
Use this script.
– This is your original table
Create table @Temp (id int, month int, value int )
insert into @Temp values (1, 1, 1)
insert into @Temp values (2, 2, 2)
insert into @Temp values (3, 3, 3)
insert into @Temp values (4, 4, 4)
insert into @Temp values (5, 5, 5)
– Lets declare a table variable which will be used for our calculations
– and a Output table Variable which will be used to store results
Declare @Cal_Table1 Table (Ident Int Identity , Month int , Value int )
Declare @Output_Result Table (Month int , Value int )
Insert Into @Cal_Table1 (Month, Value)
Select Month
, Value
From @Temp
order by Month — This is important for correct results
Declare @Count int
, @Count_Sum int
, @Previous_Val_Result int
Select @Count_Sum = SUM(1)
from @Cal_Table1
Set @Previous_Val_Result = 0
Set @Count = 1
While @Count < = @Count_Sum
Begin
If @Count = 1
Select @Previous_Val_Result = MONTH * Value
From @Cal_Table1
Where Ident = @Count
Else
Select @Previous_Val_Result = MONTH*@Previous_Val_Result
From @Cal_Table1
Where Ident = @Count
Insert into @Output_Result (Month, Value) Values (@Count, @Previous_Val_Result)
Set @Count = @Count + 1
End
Select * from @Output_Result — This is your final Result.
Let us know if you have questions.
~Peace
Hi Pinal ,
I am new to sql server , used to read you blog.
I have one requirment , can i use like operator for more than one value, in a subquery of a select stmt .
Give us more details on what you want to do
Is there any way to work around the limitation of not being able to reference into a subquery? For instance, I’m selecting a number of fields for my main query and I would also like to select several fields from the sub in that same main select statement.
Can you explain the internal functional behaviour of SQL in subquery and correlated-subqueries. e.g. :
the above given example of yours :
will the subquery be executed everytime new row fetched ? from outer query ?
or subquery is calculated and then outer query retrieves rows and evaluate against selected rows ?
Also what happens when outer row determines the contents of correlates subquery ? whats is the sequece in that time ?
Can you explain the internal functional behaviour of SQL in subquery and correlated-subqueries. e.g. :
the above given example of yours :
will the subquery be executed everytime new row fetched ? from outer query ?
or subquery is calculated and then outer query retrieves rows and evaluate against selected rows ?
Also what happens when outer row determines the contents of correlates subquery ? whats is the sequece in that time ?
Dear Sir,
I finished MCA in 2009 batch…Now only i joined software field.Sir I want to develop my skill(Sql Server). Please tell how to contact you or otherwise send the sql tricks to my mail id..
Thank you Sir
Dear,
Result will be same of both of subquery,
but Noncorrelated subquery Perform faster
Dear sir,
your blog is really nice.
you are the best in explaining the concepts…
Thank you
Sir,
Thanks for this solution . I would like to know how to call web service from stored procedure…please let me know.
Hi Pinal,
I wish you very Happy life,who is helping lot of People in this world thrg this site.
I have one Question:
what is limitaion of correlated subquery?
I can i optimize below query?
select empid,name,Deptid,
Dept_EmpCount=(select count(*) from emp e1 where e.deptid =e1.deptid)
from emp e
in this Query i am retrieving EmpId,Name ,DeptId, and number of employee in his dept .
I want to OPtimize this Query.
Please Help!!!
Regards,
Dinesh Babu (SQL DBA)
Have you tried this?
select e.empid,e.name,e.Deptid, count(*) as Dept_EmpCount from emp e inner join
(select deptid,count(*) as Dept_EmpCount from emp group by deptid) as e2 on e.deptid=e2.deptid
Hi Madhivanan,
Thanks for Reply!!!
your answer look good to me but we need to use in your query group by clause if we are using count(*)
but i want to do same think from Correlated sub query in optimized way…
Thanks,
Dinesh Babu
Compare the difference in execution time and choose the best one
Hi All,
can we have multiple MDF(primary data file) in one database?
Thanks,
Dinesh Babu
Every database can have only one primary file .However you can add as many secondary files as possible
Hello Sir,
I have one question how can we use the order by clause in the Sub queries.
Regards,
Raghav Sharma
Yes as long as you use top operator too
[...] A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. This implies that a subquery SELECT statement can stand alone, and it does not depend on the statement in which it is nested. A subquery SELECT statement can return any number of values and can be found in the column list of a SELECT statement, and FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A subquery can also be used as a parameter to a function call. Basically, a subquery can be used anywhere an expression can be used. (Read more here) [...]
Hi Dave,
For the Querf Of Pawan, for intechanging the values in a column u have given a good solution for it by making use of single query,but it works fine when both the columns are of same datatype,bt its nt wrking wen we use different types,so any conversions?
Regards,
RAM.
Can you give us an example of what you are referring?
respected sir ca n u tell me how to find out the increment in salary (percentage) from the table????
Can a subquery be used in an insert statement?
As:
insert into ______()
VALUES()
Is the above valid?
Yes like below
insert into……….
Hi Pinal,
The information in the site is very useful for us.
Can you please send some good sql tutorials( ebooks) for 3+ years exeprienced in .net tecnologies, as I have not worked much in db part.
Would like to improve on that
Hi,Madhivinan
this is the scenario wher i had a doubt of interchanging the columns?
So any solution?
create table demotable(id int,name varchar(120))
Insert demotable values(1,’abc’)
Insert demotable values(1,’def’)
select * from demotable
update demotable
set id = name,
name = id
Why are you interchanging the columns? Note that both should have same datatype decdlared to do this
Hi ,
in SQL can you explain me some security loop holes which can access the program and destroy it?
Thanks & Regards
Nikita
Sir,
I saw your website its really super it very help full to beginners and improve our knowledge this website because it can easily to understand them.
I have lots of doutes in the sql server pls send to my mail -id daily some tips and tricks in sql server sir.
Also please suggest some books in sql server and how can i get into some company as a sql developer.
plz if possible send the SQL interviw questions.
Thanks,
T.Manikuma
Hello sir,
I want to get the list of empno’s and ename who is working in NEW YORK location using non-correlated subqueries using emp and dept tables…could you let me know the sql query for this?
We all love your articles ! Its really precious as gold !!
Thanks and regards
R Jagtap
Pinal pls help me out . if a table contains some columns with id say 1,2,3 if i delete row at 2. now i want to insert new row at the id 2
thank you in advance
Its Good
The article which you have given us is very good and easy understandable please send some of the articles to my mail id .i still like to learn more tips in sqlserver
Hi Pinal,
select C1 from TA where C1 in (Select C1 from TB)
The column C1 does not exists in the table TB. but this query still qworks and all the contents of table TA are returned.
Can you tel how the query works ?
Hi Pinal,
I am very regular on your website. I have a query aorund the use of Correlated SubQuery: I want to get the department name which has more than 5 employees in it.
I can achieve this result set using Non-Correlated SubQuery OR Correlated subquery as below:
Simple Group By Clause and Joins:
select d.departmentName from DeptMaster d Inner Join new_Employee1 ne
On d.DeptId=ne.Dept Group by d.departmentName,ne.Dept having COUNT(*) >5
Coorelated Subquery:
select d.departmentName from DeptMaster d where 5 <
(
select COUNT(*) from new_Employee1 ne where ne.Dept=d.DeptId
)
My question is why should I corrleated subquery when I can achieve the same thing without it.
Is there any benefits of writting a correlated sub query.
Please help me to clear my doubt ASAP
Hi,
I have gone thru ur site,its very nice to learn.
Its very helpful to me and it cleared my concept on corelated sub query.
Thanks a lot.:)
hi panel,
can u explain which is better practice to use with our query for Better Permanence because we can do all of it with join also.