Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

All Tags » Subqueries   (RSS)
Friday, June 27, 2008 2:44 PM

Subqueries in BETWEEN and CASE Statements

Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT) CREATE TABLE T2 (A INT, B INT) SELECT * FROM T1 WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2 Observe that the subquery in this query only needs to be evaluated
Posted by craigfr | 5 Comments
Filed under:
Thursday, October 18, 2007 12:59 PM

CTEs (Common Table Expressions)

CTEs or common table expressions, which are new in SQL Server 2005, provide an easy way to break a complex SQL statement down into smaller more manageable queries. CTEs are is some ways very much like views. Unlike a view which can be created once and
Posted by craigfr | 3 Comments
Wednesday, October 04, 2006 10:11 AM

Decorrelating Subqueries

In my previous post , we saw some examples where the optimizer is able to take a query with a correlated subquery and rewrite it as a join. For instance, we saw how this simple “in” subquery: create table T1 ( a int , b int ) create table T2 ( a int ,
Posted by craigfr | 1 Comments
Filed under:
Wednesday, September 27, 2006 11:09 AM

Scalar Subqueries

A scalar subquery is a subquery that returns a single row. Some scalar subqueries are obvious. For instance: create table T1 ( a int , b int ) create table T2 ( a int , b int ) select * from T1 where T1 . a > ( select max ( T2 . a ) from T2 where T2
Posted by craigfr | 1 Comments
Filed under:
Wednesday, August 30, 2006 2:02 PM

Subqueries: ANDs and ORs

In my “Introduction to Joins” post , I gave an example of how we can use a semi-join to evaluate an EXISTS subquery. Just to recap, here is another example: create table T1 ( a int , b int ) create table T2 ( a int ) create table T3 ( a int ) select *
Posted by craigfr | 3 Comments
Filed under: ,
Wednesday, August 23, 2006 1:23 PM

Subqueries in CASE Expressions

In this post, I’m going to take a look at how SQL Server handles subqueries in CASE expressions. I’ll also introduce some more exotic join functionality in the process. Scalar expressions For simple CASE expressions with no subqueries, we can just evaluate
Posted by craigfr | 5 Comments
Filed under: ,
 
Page view tracker