The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Anything better than SQL?

A recent thread

posed an SQL query problem. Several solutions were provided.

Are there really no easier solutions in SQL? Do any databases support any query language that makes it any easier? Granted I’m a novice, but the solutions here seem unduly complex. I’m currently implementing my own DBMS, and my object model supports the following solution: 

Q =: Query.New ‘’
Q.Table := ‘SCIENTIST’
Q.Fields := 'NAME' ‘etc’
Q.AddSelection ‘XREF’  ‘BIOLOGY’  ‘SPECIALTY.DESC = “Biology”’

The AddSelection method (you can add as many as you want) takes the name of a table, an arbitrary name to represent a boolean array for later execution, and a selection statement. The trick is to perform the negation after mapping the boolean array that corresponds to XREF into SCIENTIST.

It seems to me that one of the fundamental problems with SQL is when you want to select rows from a “small” table based on criteria in a “Large table”, ie, a one to many relationship. 

Are there any commercial alternatives to SQL out there?
Freddy Hayek
Monday, January 17, 2005
SQL is horrible, but apparently doesn't itch too many people. Python is great, but everyone and their sister are busy designing lesser languages. Weird.

Nevertheless ....

xplain [ ] is slightly better, but not really different.

q and ksql [ ] are so much better, but seem to be sourrounded by a SEP field.

There are probably other alternatives I'm not aware of.
Ori Berger Send private email
Monday, January 17, 2005
You're a novice and you are making your own RDBMS.  Is that *because* you're a novice, or because you're modest?? ;-)

With your solution, you describe that "..the trick is.."; so is this less tricky than the SQL you linked to?

Entity classes rule.
i like i
Monday, January 17, 2005
The strength of SQL is that every result-set is queriable, so you can do such things as ...

select  occurs,
  select item,
          count(*) occurs
  from  my_table
  group by item
group by occurs

It's also getting extended all the time, with analytic functions and MODEL cluases (which I haven't worked out a use for yet).

However the set-oriented thinking is rether alien to most regular programmers, it seems.
David Aldridge Send private email
Monday, January 17, 2005

Thanks for info. I'll check out xplain. With respect to Kx and q, I think they are surrounded by a SEP field because they are a "call us for pricing" operation.

i like i:

I guess I'm a novice because I am making my FIRST DMBS, and I am an application and GUI programmer with little to no db experience... but I'm learning fast! 

Regarding the "trick" perhaps "technique" would have been a better word. But you be the judge: Look at the SQL solutions and compare to mine.  Which looks less complicated? If you didn't know SQL, which would you rather try and understand?
Freddy Hayek
Monday, January 17, 2005
soon databases like sql will support writing stored procedures in C#, etc...

I'm looking forward to this since I code in C# and sql, although rather simple - would be nice to consolidate  :)
Monday, January 17, 2005
I love SQL. It's an effective and mature technology. The strength of SQL is in its conceptual simplicity and being well-mapped to relational model. With SQL it's easy to build effective queries because you understand what actions they'll translate to on much lower levels. SQL is also very convenient for doing interactive queries.

BTW, the syntax suggested by OP can be easily and effectively impletemented on top of SQL.
Monday, January 17, 2005
"BTW, the syntax suggested by OP can be easily and effectively impletemented on top of SQL."

Is that true of any complex SQL query? In other words, is there any complex sql query that cannot be replaced with a much simpler syntax using a well designed object model?
Freddy Hayek
Monday, January 17, 2005
SQL is not conceptually simple.  Go beyond the simplest queries and SQL is a quagmire.

Even many of the SQL books on the market will tell you in the introduction

"sql brings databases to the masses" and in later chapters will tell you

"as a programmer or administrator you should hide all SQL complexities behind views.  Your users are guaranteed to make a lot of mistakes."

My experience with a variety of databases bears this out completely.  Even if an SQL that someone in Accounting cooked up using SQL for dummies or some such happens to be correct, it burdens the system enormously.

In these books, somewhere between the intro & the real meat of SQL the simplicity vanishes & "all the SQL complexities" arise.

And as for knowing quickly and easily that some SQL statement or other will execute faster, you better tell that to all the authors of SQL optimization books.  And all the buyers.

And all the vendors selling SQL optimization software (and their customers).  Or schema-analysis software that'll tell you where to put indexes, how to rearrange columns, when to denormalize, and so forth ...

You better also tell all the shops renting out consultants at $1000 an hour to optmize SQL that their business model is invalid.
Sanjeev Sharma
Monday, January 17, 2005
In the other thread, the solutions were more complicated than is generally necessary with SQL, because the query needed to run on MySQL.  MySQL lacks features like sub-selects and set difference queries - so other, less than straightforward, methods had to be used.  More complete SQL implementations are more useful for the type of query that was required.
Monday, January 17, 2005
+++SQL is not conceptually simple. Go beyond the simplest queries and SQL is a quagmire+++

Sanjeev, you misunderstand conceptual simplicity for just simplicity. SQL isn't at all times simple, but it builds off a few very simple concepts one can learn in a few hours. A query of any complexity is just an application of those concepts one over another, and nothing essentially new. This typically isn't so with other query languages I've seen.

Your claims about SQL being difficult to optimize might make some sense if its alternatives were any better at it. When you show me a query language you can better results with, then I'll believe you. Every alternative I've seen so far was dead-slow, or in prohibitively short in features.

++Is there any complex sql query that cannot be replaced with a much simpler syntax using a well designed object model? +++

I use an OO query builder on a regular basis, and it's never any simpler that SQL. In fact, much more difficult to read an make sense of. The only situation you'd want to use it in is when you need to extensively manipulate query at run time, far beyond just assigning the parameters. Which, fortunately, isn't often.
Monday, January 17, 2005
If you're going to implement your own RDBMS, you'd do well to read Codd and Date (or even better, Date's analyses of Codd) to learn why SQL is less than great, and how you can avoid some of its myriad problems.

SQL is computationally complete, so you might just be better advised to do like so many of the SQL-based repositories do and translate your local QL into SQL.
Art Send private email
Monday, January 17, 2005
"It seems to me that one of the fundamental problems with SQL is when you want to select rows from a “small” table based on criteria in a “Large table”, ie, a one to many relationship."

Can you explain why that's a problem? Using a subselect with EXISTS or NOT EXISTS makes it trivial; and you can do the same thing with a LEFT JOIN if subselects are not supported. SQL syntax and logic are different than procedural or OO languages, but it's very powerful.
Anony Coward
Monday, January 17, 2005
Ah, I've said it before....

I categorize programming languages into 4 basic food groups:
1 - Procedural. You know the drill, VB, C/C++/C#, PERL, Java. They go here
2 - Set based. SQL goes here. APL has half a leg in this group too.
3 - Pattern based. You've seen PERL and regexes here.
4 - Tree based. XSLT is the biggest sample of this group.

There are some people who find certain categories "hard." I'm sure you can all find a procedural programmer who can't write a SELECT statement, or a DBA who couldn't write a simple FOR loop. If you have a hard time with one group, stick to others. Personally, I find patterns to be my downfall. If the world used PERL *all* the time, I would probably be in a different career by now.
Monday, January 17, 2005
SQL is hard for a lot of people because, as someone else pointed out, you need to understand the world you're dealing with in terms of sets. SQL also makes use of the Relational Algebra, which is not easy to master, either. Think of the paradigm shift between purely procedural programming and OOP&D.

Just like anything else, it takes a bit of study and practice to become proficient at it. I think where a lot of traditional developers fall down is they read that the language and syntax of SQL is "simple" and therefore assume that building a query should be also. Well, true enough, it is simple syntactically. But the SQL language has undergone a lot of extensions since it was first invented in the '70s. Querying a database effectively can be a bear sometimes.

If you are going to be using a relational database, you need to use a querying language that can relate those normalized tables effectively. Period. SQL is verbose and clumsy, but it is also extremely stable, almost universally accepted, and a solid working knowledge of it will ensure employment.
Chris Send private email
Monday, January 17, 2005
You know another issue is that SQL is  4GL -- you're really describing what result set you want, not telling the system how to get it. This means that if you really want to understand SQL queries then you've also got to understand the particular RDBMS and the schema, and be able to read the "how it got the result" from the system (eg. with an Oracle "autotrace" or "event 10046 trace"). You've got to understand the data access mechanisms, the join mechanisms, the use of temporary sort space etc..

So being a decent writer of SQL is easy, but being an excellent writer is extremely difficult.
David Aldridge Send private email
Tuesday, January 18, 2005

If you are looking for existing OO DBMS, have a dekko at
ShyK Send private email
Tuesday, January 18, 2005
"confuse conceptual simplicity with just simplicity."

IOW, conceptual simplicity is meaningless in the real world, at the end of they day, when the rubber hits the road, on the bottom line[*].

original assertion
"with SQL it's easy to tell what's happening at a low level"

Original assertion destroyed, then becomes
"show me something simpler".

Alll- righty, then

I've been told Ingres Quel is much simpler to optimize.  Never used it myself, though.

[*] feel free to add  your own MBA/ analyst/ Dilbert-speak cliches here.
Sanjeev Sharma
Tuesday, January 18, 2005
As two posters have pointed out:
1) The original example uses MySQL that has a particularly broken implementation of SQL
2) SQL DBMS are particularly broken implementations of real RDBMS

Should SQL be improved? Yes.
Can it be improved without throwing it all away (what you propose)? Yes.

I don’t think SQL is so terribly flawed that it is broken beyond repair. To fix the problems, however, it would take significant changes to the way SQL DBMS work (i.e. make them Relational) and also break a whole bunch of existing code. Theoretically it can be fixed. Practically? I doubt it.
Dr. Mario Send private email
Tuesday, January 18, 2005
Dino seemed to suggest a working solution using SQL (in the original thread). I just tested it now in SQL Server and it worked.

The comparison of 'SQL' to 'other' 4th generation languages just seems totally bizarre to me.

This lawnmover I have is terrible compared to my socket set. The socket set is a lot better.

SQL isn't a programming language, really. It's a language for returning data from a database (or inserting, updating etc. etc.). It 'deals with' databases. And that's what the OP wants to do. And SQL can do it.
Mike MacSween
Tuesday, January 18, 2005
SQL is an implementation of the relational algebra, which is the extension to the set algebra with the selection and projection operators. Fairly simple and straight forward (if you overlook some of the mathematical subtleties of the set theory)

Think in term of sets and then SQL is always simple:

X = { x | x is the set of scientists which are not in the set of biologists }
Tuesday, January 18, 2005
The problem in my book, is that you have to define relations in the query. The DBMS should keep track of those. Then youd be left with specifying fields and conditions which would make SQL a whole lot simpler.
You should only have to declare the joins when you are doing something exceptional, that runs against the schema design.
Eric Debois
Wednesday, January 19, 2005
Technically speaking, a "relation" is actually a table or view or the result of a query, not the joins between them.

What you suggest would not always be a good thing, except for the most simple of cases. Consider how you join a CUSTOMER table to a PARTS table -- it could be through an ORDER table, it could be through an INVOICE table, it could be through a DELIVERIES table. There are many other cases also, but if specifying joins is a pain then you might consider making more use of views.
David Aldridge Send private email
Thursday, January 20, 2005

This is a 4GL tightly bound to a RDBMS in a way that (IMO) makes writing the average business app significantly easier than a typical VB/J2EE style app. There are some titanic gotchas, though.
NetFreak Send private email
Wednesday, January 26, 2005
With SQL, you specify the set you want, and the RDBMS gets it for you. For this lazy man, having to write the code to actually get the data is _alot_ more work than figuring out how to specify the set, which is what you'd have to do, anyways...

Are there better implementations of the relational algebra? Probably. Are there better querying languages? Maybe. Is there anything out there with anywhere near as much infrastructure waiting to be reused? Not in the foreseeable future.
Stephen Hirsch Send private email
Wednesday, January 26, 2005
>> Are there better implementations of the relational algebra? Probably. Are there better querying languages? Maybe. Is there anything out there with anywhere near as much infrastructure waiting to be reused? Not in the foreseeable future. <<

Good summary.

For me the advantage of SQL is just that it is supported by so many products. There's variations and different syntaxes here and there, but nearly all products adapt to the changes pretty gracefully.

Esperanto is probably a lot more regular and logical than English, but I don't see it taking over in the next thousand years. Would you choose to publish a book in Esperanto only? I think not.
David Aldridge Send private email
Wednesday, January 26, 2005
Plural vs. singular table names discussed ad nauseum...
Former COBOL Programmer
Thursday, January 27, 2005
I think it's the ability to pull from a subquery that allows one to build immensly powerful queries from simple constructs.  I'm not very good at it yet, but with help from some with more experience, I've not had a situation yet where I've been unable get the needed answer.

The statement above that SQL is about sets just turned on the light.  Rethinking SQL as specifying the right set is going to help a lot. 

Now, I am  a big fan of OO and have used some very elegant OO-relational mappers ( SQLObject and Cayenne for example).  A large part of the web applications I've dealt with are not much more than the CRUD cycle and I'm beginning to think that the OO-relational mapper is not adding all that much value.  This is probably where theory ( abstract the persistance away.... ) and reality on the keyboard meet head on.  I noticed this mostly on the pages where users are filling in forms that represent an object in the domain and one needs to implement the validation and cycling back through the editing with cancel.  The mapper just gets in the way in this case.
Kevin Dahlhausen Send private email
Tuesday, February 08, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz