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)
pinaldave
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……………
[...] 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
hi brother…. i am studying sql. Can i get solved subqueries tutorial.