SQL : Joins and Corelated Subquery

Co-related Subquery

A sub query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query. A sub query can be used anywhere an expression is allowed


/* SELECT statement built using a sub query. */
SELECT Name
FROM AdventureWorks2008R2.Production.Product
WHERE ListPrice =
(SELECT ListPrice
FROM AdventureWorks2008R2.Production.Product
WHERE Name = 'Chainring Bolts' );


/* SELECT statement built using a join that returns
the same result set. */
SELECT Prd1. Name
FROM AdventureWorks2008R2.Production.Product AS Prd1
JOIN AdventureWorks2008R2.Production.Product AS Prd2
ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2. Name = 'Chainring Bolts';

In some cases they can rank among the poorest performing SQL statements imaginable because the inner result set must be constructed for every single row that is a candidate for inclusion in the outer result set. If both the inner and outer result sets are large the amount of processing required can be huge
If a table appears only in a sub query and not in the outer query, then columns from that table cannot be included in the output.


Conclusion
There are times when a subquery is advisable, but in most cases a JOIN is better. Here is why:

1. Subqueries have to execute first, then the outer query is executed. Basically executing one subquery within your query means you are executing TWO queries instead of one. Joins would eliminate the need for this.

2. A join can take better advantage of the index. If your table is indexed on the joined column, a join is much faster.

3. For ease of reading, joins are much better.

Read More

SQL : char, nchar, nchar, nvarchar

• nchar and nvarchar can store Unicode characters.

• char and varchar cannot store Unicode characters.

• char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space.

• varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.

You can use char when the data entries in a column are expected to be the same size.
You can use varchar when the data entries in a column are expected to vary considerably in size.

Easy way to Remember
n - Can store Unicode characters
var - variable length

DECLARE @myVariable AS char(40)
SET @myVariable = 'Prasad'
SELECT DATALENGTH(@myVariable)

Answer is 40

=============================================

DECLARE @myVariable AS varchar(40)
SET @myVariable = 'Prasad'
SELECT DATALENGTH(@myVariable)

Answer is 6



To know more about UNICODE go thru Wikipedia or this article
To know more about other Data Types read this blog
A ver well written FAQ's on SQL .Must Read

Read More