August 24, 2018 at 1:53 pm
Do the fields of both objects and/or select statements between which i use EXCEPT keyword have to be or preferably (for precise results?) be named the same?
E.g. Select Col1, Col2, Col3 From Table1
EXCEPT
Select Col1, Col2, Col3 From Table2
is it identical to
Select Col1, Col2, Col3 From Table1
EXCEPT
Select MyColumn1, HisColumn2, HerColumn3 from Table2 ?
And datatypes must be same right?
Likes to play Chess
August 24, 2018 at 1:56 pm
They don't have to be the same name. Just same number of columns and same order and data type.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 24, 2018 at 1:56 pm
VoldemarG - Friday, August 24, 2018 1:53 PMDo the fields of both objects and/or select statements between which i use EXCEPT keyword have to be or preferably (for precise results?) be named the same?E.g. Select Col1, Col2, Col3 From Table1
EXCEPT
Select Col1, Col2, Col3 From Table2is it identical to
Select Col1, Col2, Col3 From Table1
EXCEPT
Select MyColumn1, HisColumn2, HerColumn3 from Table2 ?And datatypes must be same right?
EXCEPT is in the same family of operations as UNION. So you don't need to have the columns the same name but you do need them to be comparable.
August 26, 2018 at 2:11 pm
Do the fields [sic: columns] of both objects [sic: table expressions] and/or SELECT statements between which I use EXCEPT keyword have to be or preferably (for precise results?) be named the same?
The term you're looking for is "union compatible", and it means that two table expressions have to have the same number of columns, with compatible data types in the corresponding positions. The result of a set operation (UNION, INTERSECT, EXCEPT) does not have names on its columns. If you wish to use this table expression later you have to do it like this:
(SELECT col_1, col_2, col_3 FROM Table1
EXCEPT
SELECT col_1, col_2, col_3 FROM Table2)
AS Foobar(x, y, z)
If you really want to be super safe, then use CAST() to assure that the corresponding columns are the same data type.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 27, 2018 at 10:08 am
VoldemarG - Friday, August 24, 2018 1:53 PMDo the fields [sic: columns] of both objects [sic: table expressions] and/or SELECT statements between which I use EXCEPT keyword have to be or preferably (for precise results?) be named the same?
The term you're looking for is "union compatible", and it means that two table expressions have to have the same number of columns, with compatible data types in the corresponding positions. The result of a set operation (UNION, INTERSECT, EXCEPT) does not have names on its columns. If you wish to use this table expression later you have to do it like this:
(SELECT col_1, col_2, col_3 FROM Table1
EXCEPT
SELECT col_1, col_2, col_3 FROM Table2)
AS Foobar(x, y, z)
If you really want to be super safe, then use CAST() to assure that the corresponding columns are the same data type.
The result of a set operation inherits the names from the first set in the operation, so the result will have names if the first set has names, and will not have names if the first set does not have names. Naming the columns in the manner you specify is only ever necessary when you are naming the result set and one of the columns in the result set doesn't already have a name.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 27, 2018 at 10:44 am
VoldemarG - Friday, August 24, 2018 1:53 PM
The result of a set operation inherits the names from the first set in the operation, so the result will have names if the first set has names, and will not have names if the first set does not have names. Naming the columns in the manner you specify is only ever necessary when you are naming the result set and one of the columns in the result set doesn't already have a name.
Actually, the ANSI/ISO standards specify the columns are nameless. Some products inherit names from the first table in the expression, and some inherit from the last table. I am not sure which product does what anymore. When we were defining the set operations, the standards committee, we decided on the most portable option rather than favor one committee over the others. I also find it just be a good programming practice, similar to not using "SELECT *" in a query, putting the AS operator everywhere it will fit, etc.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 27, 2018 at 12:08 pm
jcelko212 32090 - Sunday, August 26, 2018 2:11 PMVoldemarG - Friday, August 24, 2018 1:53 PMThe result of a set operation inherits the names from the first set in the operation, so the result will have names if the first set has names, and will not have names if the first set does not have names. Naming the columns in the manner you specify is only ever necessary when you are naming the result set and one of the columns in the result set doesn't already have a name.
Actually, the ANSI/ISO standards specify the columns are nameless. Some products inherit names from the first table in the expression, and some inherit from the last table. I am not sure which product does what anymore. When we were defining the set operations, the standards committee, we decided on the most portable option rather than favor one committee over the others. I also find it just be a good programming practice, similar to not using "SELECT *" in a query, putting the AS operator everywhere it will fit, etc.
Yes, but this is a forum specifically for SQL Server. You do everyone here a disservice by consistently ignoring that fact.
Telling us what the standards say is a moot point, because none of us here have the ability to change how SQL Server implements the standards.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply