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 (https://blog.sqlauthority.com)
75 Comments. Leave new
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
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