One thing I keep missing in SQL

Question

One thing I keep missing in SQL

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
It's not that they "can't," it's that it was not designed to do that because in SQL we are working with sets of data. We don't want it to the above construct. That's what programming is for 🙂
Well maybe I should be clearer. What is it about that construct that is incompatible with set oriented processing?

When we are working with data, we are either selecting it, updating it, or deleting it. For data integrity, we don't want 1 statement to do both. In order for the SELECT a+b as c, d+e as a statement to work you have to define what it does. Are we first selecting a+b or updating a? The thing about SQL is that we are doing it all at once. We treat a row as an entity and work with all values of that row at 1 time. So, the said statement is just plain invalid because you cannot update and select at the same time, they are 2 different things.

Jared
CE - Microsoft

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 2

SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
It's not that they "can't," it's that it was not designed to do that because in SQL we are working with sets of data. We don't want it to the above construct. That's what programming is for 🙂
Well maybe I should be clearer. What is it about that construct that is incompatible with set oriented processing?
When we are working with data, we are either selecting it, updating it, or deleting it. For data integrity, we don't want 1 statement to do both. In order for the SELECT a+b as c, d+e as a statement to work you have to define what it does. Are we first selecting a+b or updating a? The thing about SQL is that we are doing it all at once. We treat a row as an entity and work with all values of that row at 1 time. So, the said statement is just plain invalid because you cannot update and select at the same time, they are 2 different things.

I can sort of see what you're driving at, but theres nothing wrong (in my mind anyways) with saying c means 'a+b', because you now know that c is not updateable (its an alias for an expression, sort of an anonymous function made on the fly, except it DOES have a name). SQL already sets some sorts of view constructs as unupdateable so this isn't a difficulty for me as a language implementer for instance, I assign the symbol 'c' and know at that point in use, it is unupdatable. I'm even now still not seeing what violates the set oriented nature here.

For example,

CREATE TABLE QUERY_TEST_1 (A VARCHAR(30), B VARCHAR(30))

INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_1','ORIGINAL_B_COL_INSERT_1')

INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_2','ORIGINAL_B_COL_INSERT_2')

INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_3','ORIGINAL_B_COL_INSERT_3')

INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_4','ORIGINAL_B_COL_INSERT_4')

INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_5','ORIGINAL_B_COL_INSERT_5')

SELECT A AS B, B AS A FROM QUERY_TEST_1

SELECT A+' '+B AS A FROM QUERY_TEST_1

Here we see SQL perfectly able to both disambiguate names, and select expressions on the fly.

CREATE VIEW QUERY_VIEW_1 AS SELECT A AS B, B AS A FROM QUERY_TEST_1

GO

CREATE VIEW QUERY_VIEW_2 AS SELECT A+' '+B AS C FROM QUERY_TEST_1

GO

SELECT * FROM QUERY_VIEW_1

SELECT * FROM QUERY_VIEW_2

here again we see SQL tracking names and expressions.

UPDATE QUERY_VIEW_1 SET A = 'UPDATED'

UPDATE QUERY_VIEW_2 SET C = 'UPDATED'

And even here, SQL still keeps track of what is updateable (A is, C is not).

I guess I can see as a matter of taste (and of course, an SQL standard) that the original wish wouldn't be wanted, but I'm still not seeing the implied implementation difficulties.

Jared SSC Guru Points: 61793 More actions · Answer 3

patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
It's not that they "can't," it's that it was not designed to do that because in SQL we are working with sets of data. We don't want it to the above construct. That's what programming is for 🙂
Well maybe I should be clearer. What is it about that construct that is incompatible with set oriented processing?
When we are working with data, we are either selecting it, updating it, or deleting it. For data integrity, we don't want 1 statement to do both. In order for the SELECT a+b as c, d+e as a statement to work you have to define what it does. Are we first selecting a+b or updating a? The thing about SQL is that we are doing it all at once. We treat a row as an entity and work with all values of that row at 1 time. So, the said statement is just plain invalid because you cannot update and select at the same time, they are 2 different things.
I can sort of see what you're driving at, but theres nothing wrong (in my mind anyways) with saying c means 'a+b', because you now know that c is not updateable (its an alias for an expression, sort of an anonymous function made on the fly, except it DOES have a name). SQL already sets some sorts of view constructs as unupdateable so this isn't a difficulty for me as a language implementer for instance, I assign the symbol 'c' and know at that point in use, it is unupdatable. I'm even now still not seeing what violates the set oriented nature here.
For example,
CREATE TABLE QUERY_TEST_1 (A VARCHAR(30), B VARCHAR(30))
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_1','ORIGINAL_B_COL_INSERT_1')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_2','ORIGINAL_B_COL_INSERT_2')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_3','ORIGINAL_B_COL_INSERT_3')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_4','ORIGINAL_B_COL_INSERT_4')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_5','ORIGINAL_B_COL_INSERT_5')
SELECT A AS B, B AS A FROM QUERY_TEST_1
SELECT A+' '+B AS A FROM QUERY_TEST_1
Here we see SQL perfectly able to both disambiguate names, and select expressions on the fly.
CREATE VIEW QUERY_VIEW_1 AS SELECT A AS B, B AS A FROM QUERY_TEST_1
GO
CREATE VIEW QUERY_VIEW_2 AS SELECT A+' '+B AS C FROM QUERY_TEST_1
GO
SELECT * FROM QUERY_VIEW_1
SELECT * FROM QUERY_VIEW_2
here again we see SQL tracking names and expressions.
UPDATE QUERY_VIEW_1 SET A = 'UPDATED'
UPDATE QUERY_VIEW_2 SET C = 'UPDATED'
And even here, SQL still keeps track of what is updateable (A is, C is not).
I guess I can see as a matter of taste (and of course, an SQL standard) that the original wish wouldn't be wanted, but I'm still not seeing the implied implementation difficulties.

I think the point is that an alias is mutually exclusive from a column because of syntax. In the below example a is a column and a is an alias. In the engine side (not exactly but in principle) a the colulmn is id dhg5ds42gf with a column description, whereas a the alias is id kldfglkjg54dg234 with an alias identifier. The thing is, because of the syntax they are different objects.

CREATE TABLE #test (a int, b int, d int, e int);

GO

INSERT INTO #test SELECT 1, 3, 4, 5;

GO

SELECT a + b AS c, d + e AS a

FROM #test;

Why would we ever want it to act in a different way?

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 4

The OP had this:

select

A,

B,

A * B as C,

C * 2 as D,

C + D as E

from SomeTable

Which would error out if column C does not exist, as it should. Why would we want to change the syntax from what it really should be:

select

A,

B,

A * B as C,

A * B * 2 as D,

(A * B) + (A * B * 2) as E

This is how we think it set based scripting. C is an ALIAS for A*B at the time the statement is run. We don't want C to exist before the statement is run. C is just a column label, it is not a variable. We don't want to treat it as a variable, because then we are not looking at a set. We are looking at individual variables.

Jared
CE - Microsoft

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 5

SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
patrickmcginnis59 (2/20/2012)
I'm realise I'm posting in an old thread, but I'm genuinely curious about this and maybe someone can point out what I'm missing.
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Obviously we're talking about a hypothetical programming construct, but it surely seems to be a difficulty that I'm not understanding.
Why couldn't the rows could actually come out (in terms of the original column names), a+b with the new name 'c', d+e with the new name 'a'. Obviously the original column named 'a' is no longer able to be referenced without ambiguity, but surely there could be syntax for that also. Even with old fashioned 1990's era SQL, it can disambiguate multiple columns named 'a' or 'b', why not a simple situation like this?
Now what about the query (d+e) as a, (a+b) as c? I would see THAT construct in each row (using the original row names) d+e, a+b.
So since there are some set oriented implementation internals that prevent this, I feel compelled to ask what is the computational impossibility of this construct? What about set oriented query processing prevents this from happening?
It's not that they "can't," it's that it was not designed to do that because in SQL we are working with sets of data. We don't want it to the above construct. That's what programming is for 🙂
Well maybe I should be clearer. What is it about that construct that is incompatible with set oriented processing?
When we are working with data, we are either selecting it, updating it, or deleting it. For data integrity, we don't want 1 statement to do both. In order for the SELECT a+b as c, d+e as a statement to work you have to define what it does. Are we first selecting a+b or updating a? The thing about SQL is that we are doing it all at once. We treat a row as an entity and work with all values of that row at 1 time. So, the said statement is just plain invalid because you cannot update and select at the same time, they are 2 different things.
I can sort of see what you're driving at, but theres nothing wrong (in my mind anyways) with saying c means 'a+b', because you now know that c is not updateable (its an alias for an expression, sort of an anonymous function made on the fly, except it DOES have a name). SQL already sets some sorts of view constructs as unupdateable so this isn't a difficulty for me as a language implementer for instance, I assign the symbol 'c' and know at that point in use, it is unupdatable. I'm even now still not seeing what violates the set oriented nature here.
For example,
CREATE TABLE QUERY_TEST_1 (A VARCHAR(30), B VARCHAR(30))
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_1','ORIGINAL_B_COL_INSERT_1')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_2','ORIGINAL_B_COL_INSERT_2')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_3','ORIGINAL_B_COL_INSERT_3')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_4','ORIGINAL_B_COL_INSERT_4')
INSERT INTO QUERY_TEST_1 VALUES ('ORIGINAL_A_COL_INSERT_5','ORIGINAL_B_COL_INSERT_5')
SELECT A AS B, B AS A FROM QUERY_TEST_1
SELECT A+' '+B AS A FROM QUERY_TEST_1
Here we see SQL perfectly able to both disambiguate names, and select expressions on the fly.
CREATE VIEW QUERY_VIEW_1 AS SELECT A AS B, B AS A FROM QUERY_TEST_1
GO
CREATE VIEW QUERY_VIEW_2 AS SELECT A+' '+B AS C FROM QUERY_TEST_1
GO
SELECT * FROM QUERY_VIEW_1
SELECT * FROM QUERY_VIEW_2
here again we see SQL tracking names and expressions.
UPDATE QUERY_VIEW_1 SET A = 'UPDATED'
UPDATE QUERY_VIEW_2 SET C = 'UPDATED'
And even here, SQL still keeps track of what is updateable (A is, C is not).
I guess I can see as a matter of taste (and of course, an SQL standard) that the original wish wouldn't be wanted, but I'm still not seeing the implied implementation difficulties.
I think the point is that an alias is mutually exclusive from a column because of syntax. In the below example a is a column and a is an alias. In the engine side (not exactly but in principle) a the colulmn is id dhg5ds42gf with a column description, whereas a the alias is id kldfglkjg54dg234 with an alias identifier. The thing is, because of the syntax they are different objects.
CREATE TABLE #test (a int, b int, d int, e int);
GO
INSERT INTO #test SELECT 1, 3, 4, 5;
GO
SELECT a + b AS c, d + e AS a
FROM #test;
Why would we ever want it to act in a different way?

I agree its a matter of taste. The original poster posted:

select

A,

B,

A * B as C,

C * 2 as D,

C + D as E

from SomeTable

and I was evaluating the difficulties Celko expressed.

Jared SSC Guru Points: 61793 More actions · Answer 6

I agree its a matter of taste.

It's not a matter of taste. It is that A * B AS C is not the same as A * B = C. C is just an alias, a name, a label. The value after A * B AS C is not C, it is A * B. I know it sounds like it could be symantics, but it really isn't. If you just do A * B, it still exists without an AS because the value is A * B and nothing else.

Jared
CE - Microsoft

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 7

SQLKnowItAll (2/20/2012)
The OP had this:
select
A,
B,
A * B as C,
C * 2 as D,
C + D as E
from SomeTable
Which would error out if column C does not exist, as it should. Why would we want to change the syntax from what it really should be:
select
A,
B,
A * B as C,
A * B * 2 as D,
(A * B) + (A * B * 2) as E
This is how we think it set based scripting. C is an ALIAS for A*B at the time the statement is run. We don't want C to exist before the statement is run. C is just a column label, it is not a variable. We don't want to treat it as a variable, because then we are not looking at a set. We are looking at individual variables.

Sure its a variable, but it is still referencing a column. Inside the statement, C is a new derived column. D now references C which can be statically compiled to be A * B before the first row is fetched. E references C and D in the same manner.

Everything is unambiguous to me. I could probably reproduce this entire construct with a macro processor. Given this, SQL should be able to build this entire query without fetching the first row if the engine implementers so desired this feature be implemented. Then boom, set oriented programming with less typing. Whats not to like???

Jared SSC Guru Points: 61793 More actions · Answer 8

patrickmcginnis59 (2/20/2012)
SQLKnowItAll (2/20/2012)
The OP had this:
select
A,
B,
A * B as C,
C * 2 as D,
C + D as E
from SomeTable
Which would error out if column C does not exist, as it should. Why would we want to change the syntax from what it really should be:
select
A,
B,
A * B as C,
A * B * 2 as D,
(A * B) + (A * B * 2) as E
This is how we think it set based scripting. C is an ALIAS for A*B at the time the statement is run. We don't want C to exist before the statement is run. C is just a column label, it is not a variable. We don't want to treat it as a variable, because then we are not looking at a set. We are looking at individual variables.
Sure its a variable, but it is still referencing a column. Inside the statement, C is a new derived column. D now references C which can be statically compiled to be A * B before the first row is fetched. E references C and D in the same manner.
Everything is unambiguous to me. I could probably reproduce this entire construct with a macro processor. Given this, SQL should be able to build this entire query without fetching the first row if the engine implementers so desired this feature be implemented. Then boom, set oriented programming with less typing. Whats not to like???

It is my understanding that C is NOT a derived column. It is a label of a result. That's it! When you simply select, it is just returning results, not storing data. If I do SELECT INTO or INSERT INTO, then the results are stored as the integer that is computed from A * B. However, in SELECT A * B AS C it is just A * B. There is no C, C is just a label, not an object.

Jared
CE - Microsoft

Paul White SSC Guru Points: 150467 More actions · Answer 9

For what it's worth, I didn't really see what Joe was driving at with his example either. It's pretty easy to do what the OP was asking for with APPLY (as shown earlier) or derived tables, views, CTEs...whatever. I do disagree though that SQL only deals with sets and rows pop into existence all at once. This is valid SQL, but the result is not a set:

SELECT 1 AS A, 1 AS B

UNION ALL

SELECT 1 AS A, 1 AS B

Come to think of it, anything with an ORDER BY clause or ranking function doesn't produce a set either. Separately, if rows are created instantly and simultaneously, why do these two columns contain different data?

SELECT NEWID() AS A, NEWID() AS B

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jared SSC Guru Points: 61793 More actions · Answer 10

SQL Kiwi (2/20/2012)
For what it's worth, I didn't really see what Joe was driving at with his example either. It's pretty easy to do what the OP was asking for with APPLY (as shown earlier) or derived tables, views, CTEs...whatever. I do disagree though that SQL only deals with sets and rows pop into existence all at once. This is valid SQL, but the result is not a set:
SELECT 1 AS A, 1 AS B
UNION ALL
SELECT 1 AS A, 1 AS B
Come to think of it, anything with an ORDER BY clause or ranking function doesn't produce a set either. Separately, if rows are created instantly and simultaneously, why do these two columns contain different data?
SELECT NEWID() AS A, NEWID() AS B

Why is your first example not a set? A set is simply "A group or collection of things that belong together or resemble one another or are usually found together." In set theory "The UNION of two sets is the set of elements which are in either set." Also doesn't rely on order or rank. I didn't mean that SQL "only" deals with sets, but that it was designed to deal with sets. Clearly we have other ways of dealing with individual rows, but SQL itself was designed to query and manipulate sets of data. In fact, it was specifically designed to handle large shared data sets. Maybe I am differing in my definition of a set from you, Paul.

As far as rows created instantly and simultaneously... Again, not exactly what I meant. What I meant is that in 1 statement we are nut updating a column in a row and then updating another column in that row based on the outcome of the first. This either takes 2 separate statements, or something more complicated that a simple UPDATE. I suppose I can rethink how I want to explain my thoughts on that 🙂

Jared
CE - Microsoft

Paul White SSC Guru Points: 150467 More actions · Answer 11

SQLKnowItAll (2/20/2012)
Why is your first example not a set? A set is simply "A group or collection of things that belong together or resemble one another or are usually found together." In set theory "The UNION of two sets is the set of elements which are in either set." Also doesn't rely on order or rank. I didn't mean that SQL "only" deals with sets, but that it was designed to deal with sets. Clearly we have other ways of dealing with individual rows, but SQL itself was designed to query and manipulate sets of data. In fact, it was specifically designed to handle large shared data sets. Maybe I am differing in my definition of a set from you, Paul.

I am using the definition from relational set theory that Joe & Co usually quote - so no duplicates, sets have no order...blah blah blah. In that sense, SQL deals with 'bags' rather than sets. My example could have been better in the regard; this maybe works better:

SELECT 1 AS A, 1 AS A

UNION ALL

SELECT 1 AS A, 1 AS A

As far as rows created instantly and simultaneously... Again, not exactly what I meant. What I meant is that in 1 statement we are nut updating a column in a row and then updating another column in that row based on the outcome of the first. This either takes 2 separate statements, or something more complicated that a simple UPDATE. I suppose I can rethink how I want to explain my thoughts on that 🙂

I was primarily thinking back to Joe's comments that sparked this discussion rather than wondering what -you- meant. It's not really important anyway, I was just throwing 2 cents into the stream.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jared SSC Guru Points: 61793 More actions · Answer 12

SQL Kiwi (2/20/2012)
SQLKnowItAll (2/20/2012)
Why is your first example not a set? A set is simply "A group or collection of things that belong together or resemble one another or are usually found together." In set theory "The UNION of two sets is the set of elements which are in either set." Also doesn't rely on order or rank. I didn't mean that SQL "only" deals with sets, but that it was designed to deal with sets. Clearly we have other ways of dealing with individual rows, but SQL itself was designed to query and manipulate sets of data. In fact, it was specifically designed to handle large shared data sets. Maybe I am differing in my definition of a set from you, Paul.
I am using the definition from relational set theory that Joe & Co usually quote - so no duplicates, sets have no order...blah blah blah. In that sense, SQL deals with 'bags' rather than sets. My example could have been better in the regard; this maybe works better:
SELECT 1 AS A, 1 AS A
UNION ALL
SELECT 1 AS A, 1 AS A
As far as rows created instantly and simultaneously... Again, not exactly what I meant. What I meant is that in 1 statement we are nut updating a column in a row and then updating another column in that row based on the outcome of the first. This either takes 2 separate statements, or something more complicated that a simple UPDATE. I suppose I can rethink how I want to explain my thoughts on that 🙂
I was primarily thinking back to Joe's comments that sparked this discussion rather than wondering what -you- meant. It's not really important anyway, I was just throwing 2 cents into the stream.

I'm always trying to learn from someone more experienced than me 🙂 I wonder where Joe and Co. came up with that strange definition. If I have a set of 10 apples, I don't not have a set because they are all apples lol. Thanks for your quick responses Paul.

Jared
CE - Microsoft

GSquared SSC Guru Points: 260824 More actions · Answer 13

SQLKnowItAll (2/20/2012)
SQL Kiwi (2/20/2012)
SQLKnowItAll (2/20/2012)
Why is your first example not a set? A set is simply "A group or collection of things that belong together or resemble one another or are usually found together." In set theory "The UNION of two sets is the set of elements which are in either set." Also doesn't rely on order or rank. I didn't mean that SQL "only" deals with sets, but that it was designed to deal with sets. Clearly we have other ways of dealing with individual rows, but SQL itself was designed to query and manipulate sets of data. In fact, it was specifically designed to handle large shared data sets. Maybe I am differing in my definition of a set from you, Paul.
I am using the definition from relational set theory that Joe & Co usually quote - so no duplicates, sets have no order...blah blah blah. In that sense, SQL deals with 'bags' rather than sets. My example could have been better in the regard; this maybe works better:
SELECT 1 AS A, 1 AS A
UNION ALL
SELECT 1 AS A, 1 AS A
As far as rows created instantly and simultaneously... Again, not exactly what I meant. What I meant is that in 1 statement we are nut updating a column in a row and then updating another column in that row based on the outcome of the first. This either takes 2 separate statements, or something more complicated that a simple UPDATE. I suppose I can rethink how I want to explain my thoughts on that 🙂
I was primarily thinking back to Joe's comments that sparked this discussion rather than wondering what -you- meant. It's not really important anyway, I was just throwing 2 cents into the stream.
I'm always trying to learn from someone more experienced than me 🙂 I wonder where Joe and Co. came up with that strange definition. If I have a set of 10 apples, I don't not have a set because they are all apples lol. Thanks for your quick responses Paul.

In the math of set theory, you can't have repeating items in a set. Well, you can, but it's considered one item.

So, the set {1, 1, 2} = set {1, 2}, because the repeating "1" is the same as a single "1". Different definition that a "set of apples". It gets more complicated if you define each apple individually {apple 1, apple 2, ..., apple 10} as a set.

But the stricture against repeating elements is why normal forms are defined the way they are.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 14

On the point of a row, or even multiple rows, materializing "all at once", of course it doesn't actually work that way, since CPUs don't work that way (the process instructions sequentially), and so on. The point is that you're "supposed to" build your database and code against it as if it worked that way.

That's why it takes so many hacks to get "quirky updates" to work correctly in SQL Server. Because it's really not supposed to work, and takes advantage of the physical limitations of the machine (CPU) to bypass the "proper" method of dealing with rows and elements in sets.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jared SSC Guru Points: 61793 More actions · Answer 15

the set {1, 1, 2} = set {1, 2}, because the repeating "1" is the same as a single "1".

Eh... Kind of? That is if the elements of the set are "digits." Right? However, in most of our cases, we are not concerned with the collection of 1's and 2's, but what they represent. In set theory that is extremely important. An example pointed out to me is like this: Does the set {"black" ,"noir"} have 1 element or 2 in set theory? Well, that depends on the attributes of the elements. Are we looking at words or colors? If it is a set of colors, then the set contains 1 element. If it is a set of words, then it contains 2 elements. So, in much of relational database design the primary key is used to separate the elements so that the attributes can each be treated as a unique item in a set.

Am I on the right track?

Jared
CE - Microsoft