Darren New wrote:
> SharkD wrote:
>> It's also the choice of words that are used for some of the commands 
>> that irks me.
> 
> That's because you're thinking procedurally instead of declaritively.

Indeed.

Although it still annoys me that the Haskell standard function for 
selecting elements of a list is called "filter", not "select". The 
number of times I've done something like

   filter invalid_item my_list

when in fact I should have done

   filter (not . invalid_item) my_list

is just infuriating...

Smalltalk did it right. They had "select" and "reject". Makes it quite 
clear...

> That said, SQL is indeed one of those languages for which I regularly 
> have to look up the syntax even for simple stuff. :-)

Heh, do you have any idea how many commands Oracle has that have syntax like

   ALTER TABLESPACE BEGIN BACKUP;
   ALTER DATABASE OPEN;
   ALTER SYSTEM RESETLOGS;
   etc.

??

The manual has these huge, multi-page railroad diagrams to explain the 
syntax. Sadly, nobody thought to write down WHAT THE SYNTAX *MEANS*!! >_<

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:4ae73483@news.povray.org...
>> Instead of JOIN I would have used INTERSECT.
>
> Join is closer to a union than an intersection. It's actually a cartesian 
> product. If you join a 3-row table to a 5-row table, you get a 15-row 
> table.

Technically, in T-SQL, that would be a CROSS JOIN. JOIN all by itself 
defaults to a LEFT INNER JOIN, for which the results could vary. In fact, 
JOIN all by itself wouldn't return any rows at all in that example if there 
weren't any matchs in the 5 row table.

Personally, I don't much care what the command names are in any language; 
I've always got one or two commands slipping away from me (the other day, 
POV kept giving me an error on the "cube" primitive, which I spent a good 
ten minutes on before remembering it's "box"... *sigh*)

I do wish T-SQL had better looping and some kind of sub-routine structure 
within stored procedures. I spend a lot of time avoiding doing anything 
complicated in it, unless there's some painfully urgent need to have 
execution happen on the server instead of out at the client end.

Post a reply to this message

On 10/27/2009 1:57 PM, Darren New wrote:
> SharkD wrote:
>> It's also the choice of words that are used for some of the commands
>> that irks me. Instead of SELECT I would have used GET. Instead of
>> TRIGGER I would have used EVENT.
>
> That's because you're thinking procedurally instead of declaritively.
>
> You're thinking "The server gets a bunch of rows", instead of "the
> result is the selection of the rows that meet this criteria." You're
> thinking "The server runs this code when it gets an insertion event",
> rather than "this is the code that's triggered by an insertion."

Exactly.


>> Instead of JOIN I would have used INTERSECT.
>
> Join is closer to a union than an intersection. It's actually a
> cartesian product. If you join a 3-row table to a 5-row table, you get a
> 15-row table.

There are three types of joins. In order of popularity they are, 1) 
inner, 1) outer, and 3) cartesian. Actually four types, considering that 
outer joins can be either left or right.

Inner joins (the most common) are like an intersection -- i.e. only rows 
with a positive match in both tables are returned.

Outer joins return either the entire left table or entire right table, 
as well as any intersections in the other table.

Cartesian joins are the ones that are like a union. But they're mainly 
only used for statistical purposes.


 > That said, SQL is indeed one of those languages for which I regularly
 > have to look up the syntax even for simple stuff. :-)

Yeah. For me it's a lot easier to remember objects and their methods 
than to remember a series of exact phrases verbatim. Compare this with 
public speaking, where it is more useful to refer to a basic outline of 
a speech than to try and memorize the entire thing word-for-word.

Mike

Post a reply to this message

Captain Jack wrote:
> JOIN all by itself wouldn't return any rows at all in that example if there 
> weren't any matchs in the 5 row table.

Note the difference between a relational join and the JOIN word in SQL.

A relational join is a cartesian cross-product. A JOIN in SQL requires you 
to filter the results, because you almost never ever want an actual 
cartesian cross-product.

I.e., in relational database theory, a join gives you each row of the first 
table concatenated with each row of the second table. Then you select the 
rows where the keys are equal, then you project over the rows you actually 
want. Those are the three basic reading operations in relational theory. The 
only reason "inner join" and "outer join" exist is that people don't want to 
actually normalize their databases the way that works well with relational 
theory.

SQL kind of weirds up the syntax some, but that doesn't mean you should use 
an entirely different (and incorrect) word for the operation. :-) If you're 
going to ask for a different word, ask for one that doesn't mean something 
wrong. :-)

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message

scott wrote:
> 
> when people do write out numbers 
> they are *right justified* 

Not really.

  3.44444
      4.5
    34424

is right justified as I understand it, but that wouldn't be helpful when 
adding them up.

  2.0
02

I see this more as being fixed into a grid padded with spaces on one 
side and zeros on the other. Doesn't matter which side is padded which 
way or if both are the same.

  -Shay

Post a reply to this message

SharkD wrote:
> There are three types of joins. 

There's one kind of join in relational theory. There are three or four kinds 
of joins in SQL, each of which is a join followed by a select.

I.e., inner join isn't like an intersection. It's a cartesian join, followed 
by a select where the key from the first table matches the key from the 
second table in the result.

Outer joins are peversities caused by people not normalizing columns that 
can have NULL in them. If none of your tables can have a NULL, you never 
need an outer join.

If it was an intersection, you'd never get any rows, because you wouldn't 
have two tables with identical rows in them.

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:4ae75b04@news.povray.org...
> I.e., in relational database theory, a join gives you each row of the 
> first table concatenated with each row of the second table. Then you 
> select the rows where the keys are equal, then you project over the rows 
> you actually want. Those are the three basic reading operations in 
> relational theory. The only reason "inner join" and "outer join" exist is 
> that people don't want to actually normalize their databases the way that 
> works well with relational theory.
>
> SQL kind of weirds up the syntax some, but that doesn't mean you should 
> use an entirely different (and incorrect) word for the operation. :-) If 
> you're going to ask for a different word, ask for one that doesn't mean 
> something wrong. :-)

I do sometimes miss the old days where I had a separate disk file for every 
type of data, multiple record formats with different numbers of fields and 
types of data in every file, even more separate files with indexing 
information that had to be kept in sync by the same code that acted as the 
application front end, manual locking of records and files and--

Hmm... actually, I don't think I miss that at all, come to think of it.

:D

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:4ae75bb2$1@news.povray.org...
> Outer joins are peversities caused by people not normalizing columns that 
> can have NULL in them. If none of your tables can have a NULL, you never 
> need an outer join.

Hmm... I use outer joins for optional data a lot, without respect to nulls. 
Like this:

SELECT C.Name, U.UserName
FROM tblClients AS C
OUTER JOIN tblUsers AS U ON U.UserId = C.LastEditUserID

In that case, the column LastEditUserID is zero (another way of logically 
saying Null, I s'pose) when the client row is new and has never been edited. 
I do that so that in that query I always get the client data, with the user 
name being optional.

I'll be the first to admit that DB management is not my strong suit, though.

Post a reply to this message

Captain Jack wrote:
> Technically, in T-SQL, that would be a CROSS JOIN. JOIN all by itself 
> defaults to a LEFT INNER JOIN, for which the results could vary.

Actually, I thought you couldn't do JOIN all by itself at all. Every JOIN 
has to be part of a SELECT, right?

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message

On 10/27/2009 5:03 PM, Darren New wrote:
> Actually, I thought you couldn't do JOIN all by itself at all. Every
> JOIN has to be part of a SELECT, right?

It doesn't just have to be SELECT. But it has to be one of the other 
commands, UPDATE, INSERT, etc.

Mike

Post a reply to this message