Correlated Sub-Query Vs Nested Sub-Query – Exploring Correlated Sub-Queries

In SQL every Sub-Query has two parts inner query and outer query and these query may be another sub query. Correlated sub-query is dependent upon the outer query.

The outer query and the sub-query are related typically through a WHERE statement located in the sub-query. The way a correlated sub-query works is when a reference to the outer query is found in the sub-query, the outer query will be executed and the results returned to the sub-query. The sub-query is executed for every row that is selected by the outer query.So a correlated sub query executes once for every row retrieved by the parent query.

Since correlated sub queries have to execute for every row returned in the outer query performance can be degraded. But this performance is totally based on the query and data involved. The main advantage of a correlated sub-query is that you can use it to solve problems that cannot be solved with a conventional SQL Server query.

Example:
SELECT SaleDate, Product, SalePrice,
ISNULL ((SELECT SUM (SalePrice) FROM SalesHistory sh1
WHERE Sh1.Product = sh.Product AND Sh1.SaleID < sh.SaleID), 0) AS
RunningTotal FROM SalesHistory sh ORDER BY Product, SaleID

The running total query produced by the statement is the correlated sub-query. For each product in the table, the correlated sub-query iterates the result set and sums the SalePrice for every product sold before that record in the result set.
-
*Beginning SQL Server 2008 for Developers: From Novice to Professional

Basics of Sub-Query – Sub-Query Essentials

Before we start with the above written topic let’s have a small picture of Sub-Query. A sub-query is a SQL statement which is implemented inside another SQL statement. I mean query i.e. nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query will be a Sub-Query.

Example: /* Query1. Simple Sub-Query. */
SELECT Prod_Description, Prod_Detail
FROM Product WHERE Prod_MSRP =
(SELECT Prod_MSRP FROM Product WHERE Prod_Description=
'Jumping Balls’)

In this example Select statement has been built using Sub-Query. A sub query can be used anywhere an expression is allowed.

Example: /* Query2. Simple Sub Query with expression */
SELECT POSH.SalesOrderID, POSH.OrderDate,
(SELECT MAX (POSOD.UnitPrice)
FROM PointOfSalesOrderDetail AS POSOD WHERE POSH.SalesOrderID =
POSOD.SalesOrderID) AS MaxUnitPrice
FROM PointOfSalesOrderHeader AS POSH

In this example a sub query is used as a column expression named MaxUnitPrice in a SELECT statement. At this point we can say that a sub query is also called an inner query or inner select, while the statement containing a sub query is also called an outer query or outer select.But in many T-SQL statements that include sub queries can be alternatively formulated as joins.

In T-SQL, usually there is no performance difference between a SQL statement that includes a sub query and a logically or we can say semantically equivalent version that does not. But in some cases where existence needs to be verified and must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases join approach will have better results.

Example: /* Query3. Simple SQL statement through joins (Alternate of Query1) */
SELECT PrdOne.Prod_Description, PrdOne.Prod_Detail
FROM Product AS PrdOne JOIN Product AS PrdTwo
ON (PrdOne.Prod_MSRP = PrdTwo.Prod_MSRP)
WHERE PrdTwo.Prod_Description = 'Jumping Balls’

Coming to basics, the database engine treats a sub-query as a virtual table for the execution of the query. A sub-query can be used as a table in a join statement, as a single value in a select statement, in the where clause of a SQL statement, in the having clause of a SQL statement, or incorporated in data manipulation statements.

Sub-query execution is dependent upon the nesting level of the query. The execution tree goes from inner-most queries to outer-most queries. The higher nested queries can access the results returned by the lower nested queries.
Finally we can say a sub query nested in the outer SELECT statement has the following components:
* A regular SELECT, FROM statement
* An optional WHERE, GROUP BY, HAVING clause.
-
Try above concept with SALARY, I mean Try to get second Max salary using Sub-Query. I think examples and exercise are the best ways to understand concepts. Feel free to put your queries and comments.
-
*Microsoft SQL Server 2008 Step by Step (Step by Step (Microsoft))

Advantages and disadvantages of Cursor

Cursor implementation in application, helps data manipulation easy and even they are very effective but due to some major disadvantage of Cursor normally they are not preferred.

*As we know cursor doing round trip it will make network line busy and also make time consuming methods. First of all select queries generate output and after that cursor goes one by one so round trip happen.

*Another disadvantage of cursor are there are too costly because they require lot of resources and temporary storage so network is quite busy.

Apart from these I would like to point out some great advantages of cursor if the entire result set must be transferred to the client for processing and display.

*Client-side memory : For large results, holding the entire result set on the client can lead to demanding memory requirements on client side system.

*Response time : Cursors can provide the first few rows before the whole result set is assembled. If you do not use cursors, the entire result set must be delivered before any rows are displayed by your application.

*Concurrency control :It's a general problem with current applications, If you make updates to your data and do not using cursors in your application, you must send separate SQL statements to the database server to apply the changes. This raises the possibility of concurrency problems if the result set has changed since it was queried by the client. In turn, this raises the possibility of lost updates.But Cursors act as pointers to the underlying data, and so impose proper concurrency constraints on any changes you make.

-

It is true that in some case we can avoid cursors by using PL SQL logics,I mean by implementing While loop,Tricky queries and through joins but they require great development logic.

-
*Microsoft SQL Server 2000 High Availability

What is the difference between SQL and Pl/Sql?

Simply SQL is a data oriented language for selecting and manipulating sets of data while PL/SQL is a procedural language to create applications. SQL is composed of DML and DDL while PL/SQL is composed of declared variables and looping keywords like While,IF-ELSE etc.

Other difference is Sql as the name suggest it is just structured query language whereas PLSQL is a combination of Programming language & SQL.

PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports.

The code that makes our program function is PL/SQL. The code that create datasource(Table) and manipulates the data is SQL DML. PL/SQL may call SQL to perform data manipulation.

The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.

-
*SQL Server Fundamentals for the Accidental DBA (Video Training)

What is SQL what’s its uses and its component?

Starting from basics SQL means Structured Query Language and it is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.

We can say SQL is a programming language for querying and modifying data and managing databases.

SQL allows the retrieval, insertion, updating, and deletion of data.SQL is designed for a specific purpose: to query data contained in a relational database. SQL is a set-based, declarative query language, not an imperative or conventional language such as C or BASIC.

The SQL language is sub-divided into several language elements,As Clauses,Expressions,Predicates,Queries,Statements and Whitespace.

For most common operation in Database, SQL has different grouped statements like DDL(Data Defination Language), DML(Data Manipulation Language) and DCL(Data Control Language)

Coming to critics associated with SQL It is also possible to misconstruct a WHERE on an update or delete, thereby affecting more rows in a table than desired.


For more details you can visit http://en.wikipedia.org/wiki/SQL
-
*SQL in a Nutshell (In a Nutshell (O'Reilly))