SELECT @local_variable '=' Versus SELECT @local_variable '+='
-
Hello, I am trying to find out what is the purpose of using += when selecting a local variable to the value of an expression. So for my example, I am trying to generate and PRINT a series of TSQL statements for adding notifications to alerts in my SQL Server.
When I query: SELECT name FROM msdb.dbo.sysalerts WHERE has_notification = 0;
I get 3 alerts:
Then if use
DECLARE @AlertText2 varchar(max) =''
SELECT @AlertText2 += 'EXEC msdb.dbo.sp_add_notification
@alert_name = ''' + name + ''',
@notification_method = 1,
@operator_name = ''DBA'';'
+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysalerts
WHERE has_notification = 0;
PRINT @AlertText2
~
I get the results as expected:
but instead if I use just '=' instead of '+=' when selecting the local variable i.e.
I only get 1 value i.e. the last value that is returned.
My question is, how is it adding the += returning all the 3 values instead of the last. How is it looping through? I am trying to understand the concept here.
Any help would be greatly appreciated.
Regards
Amir
-
SELECT @Var += SomeExpresion FROM SomeTable;
is a shortcut to
SELECT @Var = @Var +SomeExpresion FROM SomeTable;This is a common shortcut in many programming languages. I learned it when studying C++ in college.
- amirmir - Wednesday, March 15, 2017 12:10 PM
I'm sorry, I might have missed an important part of the answer to your question. The query is returning the 3 values using something called "pseudo cursor" which is another way of saying that SQL Server internals will read row by row and "increment" the value. In the wrong option, it's simply assigning a value to the variable row by row and keeping only the last one.
For more information about "pseudo cursors" you can read the following article: http://www.sqlservercentral.com/articles/T-SQL/74118/ - Luis Cazares - Wednesday, March 15, 2017 12:17 PM
Hi Luis, this is exactly what I was looking for, perfectly explained. I do appreciate the link to the "pseudo cursor" and while that is more internal, my confusion was with this bit. God bless!!
- amirmir - Wednesday, March 15, 2017 12:10 PMHello, I am trying to find out what is the purpose of using += when selecting a local variable to the value of an expression. So for my example, I am trying to generate and PRINT a series of TSQL statements for adding notifications to alerts in my SQL Server.
When I query: SELECT name FROM msdb.dbo.sysalerts WHERE has_notification = 0;
I get 3 alerts:Then if use
DECLARE @AlertText2 varchar(max) =''
SELECT @AlertText2 += 'EXEC msdb.dbo.sp_add_notification
@alert_name = ''' + name + ''',
@notification_method = 1,
@operator_name = ''DBA'';'
+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysalerts
WHERE has_notification = 0;
PRINT @AlertText2
~
I get the results as expected:
but instead if I use just '=' instead of '+=' when selecting the local variable i.e.
I only get 1 value i.e. the last value that is returned.
My question is, how is it adding the += returning all the 3 values instead of the last. How is it looping through? I am trying to understand the concept here.
Any help would be greatly appreciated.
Regards
Amir
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Wednesday, March 22, 2017 11:04 AMamirmir - Wednesday, March 15, 2017 12:10 PMHello, I am trying to find out what is the purpose of using += when selecting a local variable to the value of an expression. So for my example, I am trying to generate and PRINT a series of TSQL statements for adding notifications to alerts in my SQL Server.
When I query: SELECT name FROM msdb.dbo.sysalerts WHERE has_notification = 0;
I get 3 alerts:Then if use
DECLARE @AlertText2 varchar(max) =''
SELECT @AlertText2 += 'EXEC msdb.dbo.sp_add_notification
@alert_name = ''' + name + ''',
@notification_method = 1,
@operator_name = ''DBA'';'
+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysalerts
WHERE has_notification = 0;
PRINT @AlertText2
~
I get the results as expected:
but instead if I use just '=' instead of '+=' when selecting the local variable i.e.
I only get 1 value i.e. the last value that is returned.
My question is, how is it adding the += returning all the 3 values instead of the last. How is it looping through? I am trying to understand the concept here.
Any help would be greatly appreciated.
Regards
Amir
The + = notation is a left over from the early Sybase days.. It's how we first wrote (incorrectly, by the way) the left outer join outer join.. The table that is referenced on the left side (+) is the preserved table. Well, almost..
A Bit of History
Before the SQL-99 Standard, there was no Standard OUTER JOIN syntax, so you had to construct it by hand with a messy UNION in products like very early versions of DB2 from IBM like this:
SELECT sup_id, sup_name, order_amt -- regular INNER JOIN
FROM Suppliers, Orders
WHERE Suppliers.sup_id = Orders.sup_id
UNION ALL
SELECT sup_id, sup_name, CAST(NULL AS INTEGER) -- preserved rows
FROM Suppliers
WHERE NOT EXISTS
(SELECT *
FROM Orders
WHERE Suppliers.sup_id = Orders.sup_id);You have to use a NULL with the correct data type to make the UNION work, hence the CAST() functions. Some products are smart enough that just NULL by itself will be given the correct data type, but this is portable and safer.
The other alternative is to insert a constant of some sort to give a more meaningful result. This is easy in the case of a CHARACTER column, where a message like '{{NONE}}' can be quickly understood. It is much harder in the case of a numeric column, where we could have a balance with a supplier that is positive, zero, or negative because of returns and credits. There really is a difference between a vendor that we did not use and a vendor whose returns and credits canceled out its orders.
In the second edition of this book, I described the proprietary OUTER JOIN extensions in detail. Today, they are gone and replaced by the Standard syntax. The vendor extensions were all different in syntax or semantics or both. Since they are mercifully gone, I am not going to tell you about them in this edition.
The name LEFT OUTER JOIN comes from the fact that the preserved table is on the left side of the operator. Likewise, a RIGHT OUTER JOIN would have the preserved table on the right-hand side and the FULL OUTER JOIN preserves both tables.
Here is how OUTER JOINs work in Standard SQL. Assume you are given:
Table1
a b
1 w
2 x
3 y
4 zTable2
a c
1 r
2 s
3 tand the OUTER JOIN expression
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a -- JOIN condition
AND Table2.c = 't'; -- single table filter conditionWe call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the result set.
2) If all search conditions in the ON clause test TRUE for that row, then you keep it.
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates. So let us execute this by hand:
Let â—„ = passed the join search condition
Let â— = passed the filter search conditionStep One and two:
Table1 CROSS JOIN Table2
a b a c Notes
1 w 1 r â—„
1 w 2 s
1 w 3 t â—
2 x 1 r
2 x 2 s â—„
2 x 3 t â—
3 y 1 r
3 y 2 s
3 y 3 t â—„ â—
4 z 1 r
4 z 2 s
4 z 3 t â—Step three:
Table1 LEFT OUTER JOIN Table2
a b a c Notes
3 y 3 t â—„ row(s)from step two
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL â—„ removed in step two
3 y NULL NULL â—„ removed in step two
4 z NULL NULL
4 z NULL NULL
4 z NULL NULLthe final results:
Table1 LEFT OUTER JOIN Table2
a b a c
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULLThe basic rule is that every row in the preserved table is represented in the results in at least one result row.
Consider the two famous Chris Date tables from his “Suppliers and Parts†database used in his textbooks.
SupParts
sup_id part_nbr part_qty
S1 P1 100
S1 P2 250
S2 P1 100
S2 P2 250Suppliers
sup_id
S1
S2
S3If you write the OUTER JOIN with only the join predicate in the ON clause, like this:
SELECT Suppliers.sup_id, SupParts.part_nbr, SupParts.part_qty
FROM Suppliers
LEFT OUTER JOIN
SupParts
ON Supplier.sup_id = SupParts.sup_id
WHERE part_qty < 200;You get:
sup_id part_nbr part_qty
S1 P1 100
S2 P1 100but if we put the search predicate in the ON clause, we get this result.
SELECT Suppliers.sup_id, SupParts.part_nbr, SupParts.part_qty
FROM Suppliers
LEFT OUTER JOIN
SupParts
ON Supplier.sup_id = SupParts.sup_id
AND part_qty < 200;You get:
sup_id part_nbr part_qty
S1 P1 100
S2 P1 100
S3 NULL NULLAnother problem was that you could not show the same table as preserved and unpreserved in the proprietary syntax options, but it is easy in Standard SQL. For example to find the students who have taken Math 101 and might have taken Math 102:
SELECT C1.stud_nbr, C1.math_course, C2.math_course
FROM (SELECT * FROM Courses WHERE math_course = 'Math 101') AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math_course = 'Math 102') AS C2
ON C1.stud_nbr = C2.stud_nbr;A third problem is that the order of execution matters with a chain of OUTER JOINs. That is to say, ((T1 OUTER JOIN T2) OUTER JOIN T3) does not produce the same results as (T1 OUTER JOIN (T2 OUTER JOIN T3)).
The
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Wednesday, March 22, 2017 11:05 AM
Please read the questions before answering. You mistakenly explained a removed feature (which uses a different operator) instead of a feature introduced in 2008.
FYI, SQL Server used *= and =* for OUTER JOINs, while the plus sign (+) was used in Oracle and is no longer recommended (link). - jcelko212 32090 - Wednesday, March 22, 2017 11:05 AM
I think you're thinking of *= and =* like in this article:
http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspxthe += is performing concatenation as previously explained
-
Correct that this has nothing to do with joins or anything that Joe was talking about.
The += syntax is an example of a compound assignment operator; These two statements do the exact same thing:
SET @variableA = @variableA + 1;SET @variableA += 1;These two statements do the exact same thing as well:
SET @variableA = @variableA * 1;SET @variableA *= 1;
... and these:SET @variableA = @variableA - 1;SET @variableA -= 1;Here's the complete list with an explanation :
+= += (Add EQUALS) (Transact-SQL) Adds some amount to the original value and sets the original value to the result. -= -= (Subtract EQUALS) (Transact-SQL) Subtracts some amount from the original value and sets the original value to the result. *= *= (Multiply EQUALS) (Transact-SQL) Multiplies by an amount and sets the original value to the result. /= (Divide EQUALS) (Transact-SQL) Divides by an amount and sets the original value to the result. %= Modulo EQUALS (Transact-SQL) Divides by an amount and sets the original value to the modulo. &= &= (Bitwise AND EQUALS) (Transact-SQL) Performs a bitwise AND and sets the original value to the result. ^= ^= (Bitwise Exclusive OR EQUALS) (Transact-SQL) Performs a bitwise exclusive OR and sets the original value to the result. |= |= (Bitwise OR EQUALS) (Transact-SQL) Performs a bitwise OR and sets the original value to the result. "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- Alan.B - Wednesday, March 22, 2017 6:58 PM
Thank you Alan, I did take a look at this and it helps.
- jcelko212 32090 - Wednesday, March 22, 2017 11:05 AMjcelko212 32090 - Wednesday, March 22, 2017 11:04 AMamirmir - Wednesday, March 15, 2017 12:10 PMHello, I am trying to find out what is the purpose of using += when selecting a local variable to the value of an expression. So for my example, I am trying to generate and PRINT a series of TSQL statements for adding notifications to alerts in my SQL Server.
When I query: SELECT name FROM msdb.dbo.sysalerts WHERE has_notification = 0;
I get 3 alerts:Then if use
DECLARE @AlertText2 varchar(max) =''
SELECT @AlertText2 += 'EXEC msdb.dbo.sp_add_notification
@alert_name = ''' + name + ''',
@notification_method = 1,
@operator_name = ''DBA'';'
+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysalerts
WHERE has_notification = 0;
PRINT @AlertText2
~
I get the results as expected:
but instead if I use just '=' instead of '+=' when selecting the local variable i.e.
I only get 1 value i.e. the last value that is returned.
My question is, how is it adding the += returning all the 3 values instead of the last. How is it looping through? I am trying to understand the concept here.
Any help would be greatly appreciated.
Regards
Amir
The + = notation is a left over from the early Sybase days.. It's how we first wrote (incorrectly, by the way) the left outer join outer join.. The table that is referenced on the left side (+) is the preserved table. Well, almost..
A Bit of History
Before the SQL-99 Standard, there was no Standard OUTER JOIN syntax, so you had to construct it by hand with a messy UNION in products like very early versions of DB2 from IBM like this:
SELECT sup_id, sup_name, order_amt -- regular INNER JOIN
FROM Suppliers, Orders
WHERE Suppliers.sup_id = Orders.sup_id
UNION ALL
SELECT sup_id, sup_name, CAST(NULL AS INTEGER) -- preserved rows
FROM Suppliers
WHERE NOT EXISTS
(SELECT *
FROM Orders
WHERE Suppliers.sup_id = Orders.sup_id);You have to use a NULL with the correct data type to make the UNION work, hence the CAST() functions. Some products are smart enough that just NULL by itself will be given the correct data type, but this is portable and safer.
The other alternative is to insert a constant of some sort to give a more meaningful result. This is easy in the case of a CHARACTER column, where a message like '{{NONE}}' can be quickly understood. It is much harder in the case of a numeric column, where we could have a balance with a supplier that is positive, zero, or negative because of returns and credits. There really is a difference between a vendor that we did not use and a vendor whose returns and credits canceled out its orders.
In the second edition of this book, I described the proprietary OUTER JOIN extensions in detail. Today, they are gone and replaced by the Standard syntax. The vendor extensions were all different in syntax or semantics or both. Since they are mercifully gone, I am not going to tell you about them in this edition.
The name LEFT OUTER JOIN comes from the fact that the preserved table is on the left side of the operator. Likewise, a RIGHT OUTER JOIN would have the preserved table on the right-hand side and the FULL OUTER JOIN preserves both tables.
Here is how OUTER JOINs work in Standard SQL. Assume you are given:
Table1
a b
1 w
2 x
3 y
4 zTable2
a c
1 r
2 s
3 tand the OUTER JOIN expression
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a -- JOIN condition
AND Table2.c = 't'; -- single table filter conditionWe call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the result set.
2) If all search conditions in the ON clause test TRUE for that row, then you keep it.
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates. So let us execute this by hand:
Let â—„ = passed the join search condition
Let â— = passed the filter search conditionStep One and two:
Table1 CROSS JOIN Table2
a b a c Notes
1 w 1 r â—„
1 w 2 s
1 w 3 t â—
2 x 1 r
2 x 2 s â—„
2 x 3 t â—
3 y 1 r
3 y 2 s
3 y 3 t â—„ â—
4 z 1 r
4 z 2 s
4 z 3 t â—Step three:
Table1 LEFT OUTER JOIN Table2
a b a c Notes
3 y 3 t â—„ row(s)from step two
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL â—„ removed in step two
3 y NULL NULL â—„ removed in step two
4 z NULL NULL
4 z NULL NULL
4 z NULL NULLthe final results:
Table1 LEFT OUTER JOIN Table2
a b a c
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULLThe basic rule is that every row in the preserved table is represented in the results in at least one result row.
Consider the two famous Chris Date tables from his “Suppliers and Parts†database used in his textbooks.
SupParts
sup_id part_nbr part_qty
S1 P1 100
S1 P2 250
S2 P1 100
S2 P2 250Suppliers
sup_id
S1
S2
S3If you write the OUTER JOIN with only the join predicate in the ON clause, like this:
SELECT Suppliers.sup_id, SupParts.part_nbr, SupParts.part_qty
FROM Suppliers
LEFT OUTER JOIN
SupParts
ON Supplier.sup_id = SupParts.sup_id
WHERE part_qty < 200;You get:
sup_id part_nbr part_qty
S1 P1 100
S2 P1 100but if we put the search predicate in the ON clause, we get this result.
SELECT Suppliers.sup_id, SupParts.part_nbr, SupParts.part_qty
FROM Suppliers
LEFT OUTER JOIN
SupParts
ON Supplier.sup_id = SupParts.sup_id
AND part_qty < 200;You get:
sup_id part_nbr part_qty
S1 P1 100
S2 P1 100
S3 NULL NULLAnother problem was that you could not show the same table as preserved and unpreserved in the proprietary syntax options, but it is easy in Standard SQL. For example to find the students who have taken Math 101 and might have taken Math 102:
SELECT C1.stud_nbr, C1.math_course, C2.math_course
FROM (SELECT * FROM Courses WHERE math_course = 'Math 101') AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math_course = 'Math 102') AS C2
ON C1.stud_nbr = C2.stud_nbr;A third problem is that the order of execution matters with a chain of OUTER JOINs. That is to say, ((T1 OUTER JOIN T2) OUTER JOIN T3) does not produce the same results as (T1 OUTER JOIN (T2 OUTER JOIN T3)).
The
Hello Jcelko, thank you taking the time and explaining in such great detail, though your post is not the exact answer to my question, I still appreciate your time and effort to contribute. Your time is very much appreciated!
Regards
Amir -
No problem. Glad to help.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply