Using CONTEXT_INFO
Although
SQL Server enables you to define local variables within a T-SQL batch
or stored procedure, local variables do not retain values between
batches or stored procedures. Unfortunately, SQL Server 2008 does not
enable you to create user-defined global variables. However, you can
simulate global variables by using the CONTEXT_INFO setting, which allows you to store information in the context_info column in the sys.sysprocesses catalog view. A row in sys.sysprocesses exists for every connection to SQL Server, so the data remains there until you disconnect from SQL Server.
The context_info column is a binary (128) column. You can store any data value in it with the SET CONTEXT_INFO
command, but you have to deal with hexadecimal data when retrieving it.
If you are handy at manipulating hexadecimal data, you can store
multiple values in the context_info column. The following example stores the average price from the titles table in the context_info column:
declare @avg_price money
select @avg_price = avg(price) from dbo.titles
set context_info @avg_price
You can retrieve the value stored in context_info by using a SELECT statement. You need to convert the binary data back to money when you retrieve it. Because avg(price) is the only value stored in context_info, you can retrieve it by performing a substring on the first 8 bytes of the context_info column. (The money data type is 8 bytes in size.) Because SQL Server assigns a unique server process ID (SPID) to each connection, you use the @@SPID function to retrieve the information for the current connection:
select convert(money, substring(context_info, 1, 8)) as AVG_PRICE
from master..sysprocesses
where spid = @@spid
go
AVG_PRICE
---------------------
0.3751
If you don’t use a substring to specify only the first 8 bytes of the context_info column, SQL Server assumes that the money data is stored in the last 8 bytes and returns a result of 0:
select convert(money, context_info) as AVG_PRICE
from master..sysprocesses
where spid = @@spid
go
AVG_PRICE
---------------------
0.00
Because money can be implicitly converted to binary, you don’t need to convert it when setting context_info. For some other data types, such as char or datetime, you need to explicitly convert the data to binary because implicit conversions from those data types to binary is not supported. In the following example, you append a datetime value to the average price value already stored in context_info. You explicitly convert the datetime value to binary and append it to the 8 bytes you have already stored in context_info:
declare @max_date datetime,
@context_info binary(128)
select @max_date = max(pubdate) from dbo.titles
select @context_info = substring(context_info, 1, 8)
+ convert(binary(8), @max_date)
from master..sysprocesses
where spid = @@spid
set context_info @context_info
You now have two values stored in context_info. Using the appropriate substring, you can retrieve either the average price or the maximum pubdate from context_info:
declare @avg_price money,
@max_pubdate datetime
select @avg_price = substring(context_info, 1, 8),
@max_pubdate = substring(context_info, 9, 8)
from master..sysprocesses
where spid = @@spid
select @avg_price as 'Avg Price', @max_pubdate as 'Max PubDate'
go
Avg Price Max PubDate
--------------------- -----------------------
0.3751 2009-05-31 00:00:00.000
Note that the binary data converts implicitly to money and datetime.
Working with Outer Joins
An outer join is used to return all the rows from the specified outer table (specified with LEFT OUTER, RIGHT OUTER, or FULL OUTER),
even if the other table has no match. Rows returned from the outer
table that have no corresponding match in the inner table display the
value NULL for any columns
retrieved from the inner table. For example, you might want to display
the names of all authors along with the average royalty paid, if
available:
select au_lname, au_fname, avg(royaltyper) as avg_royalty
from dbo.authors a left outer join dbo.titleauthor ta on a.au_id = ta.au_id
group by au_lname, au_fname
order by 3
go
au_lname au_fname avg_royalty
---------------------------------------- -------------------- -----------
Greene Morningstar NULL
Greenfield Tom NULL
McBadden Heather NULL
Smith Meander NULL
Stringer Dirk NULL
Gringlesby Burt 30
O'Leary Michael 35
Ringer Anne 37
Yokomoto Akiko 40
MacFeather Stearns 42
Hunter Sheryl 50
Dull Ann 50
Bennet Abraham 60
Green Marjorie 70
DeFrance Michel 75
Karsen Livia 75
Ringer Albert 75
Panteley Sylvia 100
White Johnson 100
Straight Dean 100
Locksley Charlene 100
Carson Cheryl 100
Blotchet-Halls Reginald 100
del Castillo Innes 100
Note that no real
difference exists between left and right outer joins except for
specifying which table on which side of the join condition is to be the
controlling, or outer, table. For example, the previous query would
provide the same result if you reversed the tables in the join clause
and made it a right outer join:
select au_lname, au_fname, avg(royaltyper)
from dbo.titleauthor ta right outer join dbo.authors a on ta.au_id = a.au_id
group by au_lname, au_fname
order by 3
A full outer join returns
all matching rows from both tables, along with all rows from each table,
without a corresponding match in the other table:
select a.au_fname, p.pub_name
from dbo.authors a full outer join dbo.publishers p on a.state = p.state
go
au_fname pub_name
-------------------- ----------------------------------------
James A. NULL
Francine du Plessix Kumquat Technical Publishing
Jean NULL
E.L. Nordome Titles
Justin Algodata Infosystems
April Sidney's Books and More
Ron Algodata Infosystems
Jack NULL
Matt NULL
Josef NULL
...
Albert Ramona Publishers
NULL Gooseberry Titles
NULL Binnet & Hardley
NULL GGG&G
NULL Lucerne Publishing
NULL Tomato Books
NULL Significant Titles Company
OUTER JOIN Versus WHERE Clause Matching
With the ANSI join syntax, you specify the join condition in the FROM clause. Additional search conditions can be specified in either the JOIN clause or WHEREON clause work differently than search conditions in the WHERE clause. It’s important to remember that in a left or right outer join, search conditions specified in the clause:
Needless to say, it can be somewhat confusing to figure out which rows
and data values will be returned. To demonstrate the use of search
conditions in the WHERE clause versus the ON clause, the examples presented in this section make use of the two views shown in Listing 3.
Listing 3. CREATE VIEW Statements for Outer Join Examples
CREATE VIEW dbo.STORES_V1 AS
SELECT STOR_ID, CITY FROM DBO.STORES WHERE STOR_ID BETWEEN 'A001' AND 'A005'
go
CREATE VIEW dbo.STORES_V2 AS
SELECT STOR_ID, STATE FROM DBO.STORES WHERE STOR_ID BETWEEN 'A002' AND 'A007'
UNION ALL
SELECT 'A004', 'MA'
go
select * from dbo.stores_v1
select * from dbo.stores_v2
STOR_ID CITY
------- --------------------
A001 Dublin
A002 Oakland
A003 Bisbee
A004 White Plains
A005 Thomaston
(5 row(s) affected)
STOR_ID STATE
------- -----
A002 NJ
A003 AZ
A004 NY
A005 GA
A006 CA
A007 CA
A004 MA
|
Listing 4 provides an example of a left outer join query that includes the search condition in the ON clause. All rows are returned from STORES_V1, and NULL values are returned for STORES_V2 where there isn’t a matching STOR_ID or where there is a matching STOR_ID but STATE is not equal to 'GA'.
Listing 4. Specifying the Search Condition in the ON Clause
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
AND V2.STATE <> 'GA'
ORDER BY V1.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A001 Dublin NULL NULL
A002 Oakland A002 NJ
A003 Bisbee A003 AZ
A004 White Plains A004 MA
A004 White Plains A004 NY
A005 Thomaston NULL NULL
|
Now, if you put the search condition in the WHERE clause, as shown in Listing 5, the nonmatching rows from STORES_V2 are filtered out because the NULL value returned for STATE does not match the WHERE clause search criteria WHERE V2.STATE <> 'GA'. (Remember that NULL values are neither equal to nor not equal to other values.)
Listing 5. Specifying a Search Condition in a WHERE Clause
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
WHERE V2.STATE <> 'GA'
ORDER BY V1.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A002 Oakland A002 NJ
A003 Bisbee A003 AZ
A004 White Plains A004 MA
A004 White Plains A004 NY
|
To include the nonmatching rows from STORES_V2, you need to add a check for NULL to the WHERE clause, as shown in Listing 6.
Listing 6. Including Nonmatching Inner Table Rows That Do Not Match a WHERE Clause Search Condition
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
WHERE (V2.STATE <> 'GA' or V2.STATE is NULL)
ORDER BY V1.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A001 Dublin NULL NULL
A002 Oakland A002 NJ
A003 Bisbee A003 AZ
A004 White Plains A004 MA
A004 White Plains A004 NY
|
Notice that the row where STOR_ID = 'A005' is still not included in the result set. The query shown in Listing 6 also demonstrates another difference between specifying a search condition in the WHERE clause and the ON clause. In this case, the outer join is performed first, so that all rows and selected column values are returned from STORES_V2, including the row where STOR_ID = 'A005', without considering the WHERE clause condition:
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A001 Dublin NULL NULL
A002 Oakland A002 NJ
A003 Bisbee A003 AZ
A004 White Plains A004 NY
A004 White Plains A004 MA
A005 Thomaston A005 GA
After the join result is returned, because the STATE value is equal to 'GA', the search condition in the WHERE clause (V2.STATE <> 'GA') filters out that row.
In a left or right outer join, a search condition in the ON clause also works differently, depending on whether it refers to a field in the inner or outer table. If the ON
clause search condition refers to a field in the outer table, it
determines whether the related row finds a match. (The outer row is
returned regardless.) For example, Listing 7 demonstrates a search condition in the ON clause that restricts which rows in STORES_V1 (the outer table) join to STORES_V2. The join is performed only for those rows in STORES_V1 where CITY is greater than N. However, all rows from STORES_V1 are still returned.
Listing 7. Specifying an ON Clause Search Condition on the Outer Table
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
AND V1.CITY > 'N'
ORDER BY V1.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A001 Dublin NULL NULL
A002 Oakland A002 NJ
A003 Bisbee NULL NULL
A004 White Plains A004 MA
A004 White Plains A004 NY
A005 Thomaston A005 GA
|
If the ON clause search condition refers to a field in the inner table, it determines whether the related row matches the join. Listing 8 shows an example of specifying the ON clause search condition on the inner table. Again, notice that all rows from STORES_V1 are returned, but only the matching rows are returned from STORES_V2, where STOR_ID is less than A004.
Listing 8. Specifying an ON Clause Search Condition on the Inner Table
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
AND V2.STOR_ID < 'A004'
ORDER BY V1.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A001 Dublin NULL NULL
A002 Oakland A002 NJ
A003 Bisbee A003 AZ
A004 White Plains NULL NULL
A005 Thomaston NULL NULL
|
When you perform a left or right outer join, a search condition against the outer table in the ON clause works differently from the same search condition specified in the WHERE clause. As shown in Listing 8, when the search condition in the ON
clause filters on a field in the outer table, the outer row is
returned, regardless, with no matching rows returned from the inner
table. However, if the search condition on the OUTER table is specified in the WHERE clause, the outer row is eliminated from the result set, as shown in Listing 9.
Listing 9. Specifying an Outer Table Search Condition in the WHERE Clause
SELECT * FROM DBO.STORES_V1 V1
LEFT OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
WHERE V1.CITY > 'N'
ORDER BY V1.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A002 Oakland A002 NJ
A004 White Plains A004 MA
A004 White Plains A004 NY
A005 Thomaston A005 GA
|
Nested Outer Joins
When using the ANSI JOIN
syntax, you must be careful about mixing outer joins with inner joins
because the query may not generate the desired result if the outer join
is not continued down to the lowest level. For example, consider the
query shown in Listing 10. The intent of this query is to list all authors whose state equals NE and, for those authors, display the titles of their associated books, if any.
Listing 10. An Outer Join Nested in an Inner Join
select left(au_lname, 20) as au_lname, au_fname, left(title, 20) as title
from dbo.authors a
left join dbo.titleauthor ta on a.au_id = ta.au_id
join dbo.titles t on ta.title_id = t.title_id
where a.state = 'NE'
go
au_lname au_fname title
-------------------- -------------------- --------------------
Patterson Richard North The Lasko Tangent
Patterson Richard North The Outside Man
Patterson Richard North Private Screening
Patterson Richard North Eyes of a Child
Patterson Richard North Degree of Guilt
Patterson Richard North Escape the Night
|
However, because the outer join is not carried to the titles table, the author with no matching row in the titleauthor table ends up getting filtered from the result set. The reason is that when the outer join is performed between authors and titleauthor, because no matching row is found, NULL is returned for title_id. Because a normal inner join is performed on the titles table, the NULL value for title_id does not match any rows in the titles table, so the author with no matching rows in titleauthor ends up getting filtered out. To see those authors, you need to modify the query in Listing 10 to carry the outer join down to the join between titleauthor and authors:
select left(au_lname, 20) as au_lname, au_fname, left(title, 20) as title
from dbo.authors a
left join dbo.titleauthor ta on a.au_id = ta.au_id
left join dbo.titles t on ta.title_id = t.title_id
where a.state = 'NE'
go
au_lname au_fname title
-------------------- -------------------- --------------------
Patterson Richard North The Lasko Tangent
Patterson Richard North The Outside Man
Patterson Richard North Private Screening
Patterson Richard North Eyes of a Child
Patterson Richard North Degree of Guilt
Patterson Richard North Escape the Night
McBadden Heather NULL
Working with Full Outer Joins
A full outer join selects
rows from both tables and joins those rows that match on the join
fields. In addition to the matching rows, one copy of each nonmatching
row from each table is returned. Listing 11 shows an example of a full outer join.
Listing 11. Full Outer Join Example
SELECT * FROM DBO.STORES_V1 V1
FULL OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
ORDER BY V1.STOR_ID ,V2.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
NULL NULL A006 CA
NULL NULL A007 CA
A001 Dublin NULL NULL
A002 Oakland A002 NJ
A003 Bisbee A003 AZ
A004 White Plains A004 MA
A004 White Plains A004 NY
A005 Thomaston A005 GA
|
As you can see from the results in Listing 11, all rows are returned from both STORES_V1 and STORES_V2.
In a full outer join, a search condition in the ON clause is handled differently from a search condition in the WHERE
clause in that it never results in a row being excluded from the result
set. All it does is categorize the input row as being either matching
or nonmatching. In Listing 12, a search condition (V1.STOR_ID > 'A003') is specified in the ON clause. As you can see, any rows that do not meet that search condition are returned as nonmatching rows.
Listing 12. Specifying a Search Condition in a Full Outer Join ON Clause
SELECT * FROM DBO.STORES_V1 V1
FULL OUTER JOIN DBO.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
AND V1.STOR_ID > 'A003'
ORDER BY V1.STOR_ID ,V2.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
NULL NULL A002 NJ
NULL NULL A003 AZ
NULL NULL A006 CA
NULL NULL A007 CA
A001 Dublin NULL NULL
A002 Oakland NULL NULL
A003 Bisbee NULL NULL
A004 White Plains A004 MA
A004 White Plains A004 NY
A005 Thomaston A005 GA
|
As you can see, when you work with full outer joins, search arguments in the ON clause serve only to define which rows are used as matching rows for a join. Search arguments specified in the WHERE clause define which rows are returned. It’s important to note that the WHERE clause conditions behave as if the join is an inner join and are applied after the join is done. Listing 13 shows an example.
Listing 13. Specifying a Search Condition in a Full Outer Join WHERE Clause
SELECT * FROM dbo.STORES_V1 V1
FULL OUTER JOIN dbo.STORES_V2 V2
ON V1.STOR_ID = V2.STOR_ID
WHERE V1.STOR_ID > 'A003'
ORDER BY V1.STOR_ID ,V2.STOR_ID ,V2.STATE
go
STOR_ID CITY STOR_ID STATE
------- -------------------- ------- -----
A004 White Plains A004 MA
A004 White Plains A004 NY
A005 Thomaston A005 GA
|
