Correlated and Subqueries

What is SQL Query

SQL is a Standard Query Language for storing, manipulating and retrieving data in databases.

SQL is a programming language(Query programing language) like PHP, Java, .net etc language use for application development similar SQL use for database only.

SQL also allow language depend activity like variable declaration, loops, decision making, transactions etc.

Types of SQL Language :

Database wise SQL language will be changed but ANSI Standards Command will be same in all Relational database like SELECT,UPDATE,DELETE,DROP etc.

Techniques and syntax will be vary according to Database like oracle database support to PL-SQL so if we want programing with Oracle then we need to use PL-SQL Or if want MS SQL Server Database then T-SQL similar if want cassandra then there csql will be use.

How many types of Query available in Sql Server:

In Simple word Query have only 2 types.

1. Basic Query like Select,update,delete etc. single statements.
2. Complex Query: where we use more then 1 table and use joins,merge statements, depend queries etc.

What is Correlated and Sub queries:

Correlated and Subqueries both is part of Complex query where we used more then 1 tables and there one or more tables are based table and other one or more tables may be executing based on base table or independents and this one technique we be called Correlated or Subqueries.

Correlated:

Correlated like inner query and its process row-by-row with outer query means totally dependent outer query

Exp. 1:

Exp. 2:  

Subquery :

Subquery is just like inner query but its work independent and executed only one time with outer query.

Ex. 1:

select id,name from vouchertype 
where id in (select VoucherTypeID 
from voucher
where datestamp BETWEEN ‘20140401’ and  ‘20140430’)

Ex. 2:

select a.id,a.name,b.amount from vouchertype  a
inner join (select VoucherTypeID,sum(amount)amount 
                     from voucher
                     where datestamp BETWEEN ‘20140401’ and  ‘20140430’
group by VoucherTypeID ) b on a.id=b.VoucherTypeID
 
Subquery and correlated query also can use in DML operation like select, delete, Insert and update operation.

Correlated update:

alter table vouchertype_1 add total_amt numeric(18,2)
go                    
UPDATE vouchertype_1
set total_amt = (select sum(amount)  from voucher b
                       where b.datestamp BETWEEN ‘20140401’ and  ‘20140430’
                       and vouchertype_1.id=b.VoucherTypeID)
 where id in (-105,-104,-103)

Correlated with Exists: it will return a rows from vouchertype table which is ids exists in voucher table. 

select id,name
from vouchertype  a
where EXISTS (select *  from voucher b
                       where b.datestamp BETWEEN ‘20140401’ and  ‘20140430’
                       and a.id=b.VoucherTypeID )

This one query also can use with IN operator

select id,name
from vouchertype  a
where a.id IN (select VoucherTypeID  from voucher b
                       where b.datestamp BETWEEN ‘20140401’ and  ‘20140430’  )

Correlated with not Exists: it will return a rows from vouchertype table which is ids not exists in voucher table. 

select id,name
from vouchertype  a
where NOT EXISTS (select *  from voucher b
                       where b.datestamp BETWEEN ‘20140401’ and  ‘20140430’
                       and a.id=b.VoucherTypeID )

This one query also can use with NOT IN operator

select id,name
from vouchertype  a
where a.id NOT IN (select VoucherTypeID  from voucher b
                       where b.datestamp BETWEEN ‘20140401’ and  ‘20140430’  )