February 20, 2012 at 1:14 pm
SQLKnowItAll (2/20/2012)
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?
Yep. You're on the right track.
I was using a very simplified example to make the point. I assumed you'd figure out it was a set of numbers (which you did).
- 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
February 20, 2012 at 4:56 pm
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
I like the characterization of thinking in terms of columns instead of rows that Jeff has in his sig, that catches the sense of SQL's benefits without the strictness of sets. And I think the OP's idea wasn't at all in conflict with that paradigm, however I do now get a sense of some folks difficulty getting a mental model of the textual substitutions that would need to occur for that construct to work. I know I've toyed with writing a program translator that would generate SQL as its target with the goal of playing loose with namespaces and expression substitution as I've always been dissatisfied with SQL's rather flat namespace and poor code reuse, and the OP's suggestion sort of hit the same gripe zones I've had, thus my post exploring the feasibility. I'm still too new to SQL to do much about my dissatisfaction with the language however 🙂
February 21, 2012 at 11:04 pm
CELKO (2/21/2012)
Then why does UPDATE Foobar SET a = b, b = a; swap the values of a and b? Answer: rows appear all at once!
The other interpretation (by Ken Henderson) is that the semantic is for column references on the right hand side to always return values before the update took place.
DELETE FROM Woebegone WHERE iq <= (SELECT AVG(iq) FROM Woebegone); You do not re-compute the average IQ over and over, a row at a time. Do it once and apply it to all the rows as a set, not a sequence.
This example is confused too. Another interpretation could be that subqueries are evaluated first, and the AVG result passed as a constant to the outer expression. To be clear, I am not saying this is true, just that alternative explanations exist. Another point is that SQL does not (always) deal with sets. It often does deal with sequences directly (we have Sequence Project for ranking functions), and more often deal with bags, which the relational model says almost nothing about.
Guys, trust me; after a decade of reading and voting on SQL standards, consulting and teaching, I am sure about the language not having left-to-right processing in it.
Immediate counter-example: CASE expressions, which are evaluated left-to-right.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 22, 2012 at 7:14 am
CELKO (2/21/2012)
Guys, trust me; after a decade of reading and voting on SQL standards, consulting and teaching, I am sure about the language not having left-to-right processing in it.
I guess you've never written Common Table Expressions, which evaluate left-to-right (later CTEs can reference earlier ones in a left-right/top-bottom sequence), nor used either of the Apply operators, which do the same, nor written a query that uses Joins (which evaluate left-right in terms of which columns/objects are available to use in the On clause), nor Case statements, nor Coalesce/IsNull, nor ever written a mathematical expression, and so on.
In other words, yes it does evaluate left-right, but it does so inconsistently because of the desire to create the illusion that rows and sets are materialized "all at once".
Also, "trust me" followed by a list of credentials, is "argument by authority", which has no place in a technical discussion. In philosophy or religion, that's acceptable as a debate technique, since the rules of logic can be suspended in those subject (so long as all participants agree to that stipulation or at least know it's being applied). But not in a technical discussion.
- 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
February 23, 2012 at 3:12 pm
Samuel Vella (2/8/2012)
One thing I would love to be able to do in SQL is this:(Simple example)
select
A,
B,
A * B as C,
C * 2 as D,
C + D as E
from SomeTable
instead of this which SQL forces you to do:
select
A,
B,
A * B as C,
(A * B) * 2 as D,
(A * B) + ((A * B) * 2) as E
from SomeTable
Usually I end up with either CTE's or similar to try and make the SQL more readable.
Are there any better work arounds?
I believe that someone has already suggested Views, but I don't see where Computed Columns were mentioned yet. This would at least prevent the need to repeat commonly expressions across multiple statements.
create table SomeTable
(
A int,
B int,
C as (A * B),
D as ((A * B) * 2),
E as (A * B) + ((A * B) * 2)
);
insert into SomeTable( A, B ) values( 2, 3 );
select * from SomeTable;
A B C D E
-- -- -- -- --
2 3 6 12 18
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply