Home » Other » Community Hangout » Why not remove the word "where" from the SQL statement
Why not remove the word "where" from the SQL statement [message #614420] Wed, 21 May 2014 10:10 Go to next message
Amine
Messages: 274
Registered: March 2010
Senior Member

Hi all,
I was asking myself why the new standards of SQL have always the word "where" in there specifications.
In the first times it was called (Structured English Query Language), so the SQL statement "had" to look like an English sentence.

But now, the SQL is more and more technical. And where is the problem if we write the SQL statement as this :

select a.*
from a, b
and a.id = b.id
and a.cpt > 1;


Thanks in advance for sharing your opinions,

Amine
Re: Why not remove the word "where" from the SQL statement [message #614426 is a reply to message #614420] Wed, 21 May 2014 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59974
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand what you claim it is in the new standard and what is your point.
"the new standards of SQL have always the word "where" in there specifications."
" And where is the problem if we write the SQL statement as this " which does not contain WHERE.

You want or not WHERE?
Do you say that the new standard does not contain WHERE (post a link)?
Or do you say you want a new standard without WHERE?

Re: Why not remove the word "where" from the SQL statement [message #614445 is a reply to message #614426] Wed, 21 May 2014 10:44 Go to previous messageGo to next message
Amine
Messages: 274
Registered: March 2010
Senior Member

My proposition is the proposition 3 (you want a new standard without WHERE) and this is my last word ! Smile
Re: Why not remove the word "where" from the SQL statement [message #614448 is a reply to message #614445] Wed, 21 May 2014 10:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
But don't you think "WHERE" is more appropriate in terms of english for the predicate? "AND" have had always been a condition to add to the predicate.

Even if you compare this to English language, you will fimd it true with subject-predicate rules. Anything else you have to say? I would be glad to know your thoughts. By the way, it has least to do with the Structured query language Razz
Re: Why not remove the word "where" from the SQL statement [message #614449 is a reply to message #614445] Wed, 21 May 2014 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59974
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why? In this case this is no more English.
"SELECT something FROM something WHERE something AND something... " is perfectly clear for me (and perfectly in the English I understand). Replacing WHERE by AND is meaningless, you mix up data sources and conditions.
This is like replacing "I want people with blue eyes" by "I want people and blue eyes" which does not mean the same thing.

Re: Why not remove the word "where" from the SQL statement [message #614450 is a reply to message #614445] Wed, 21 May 2014 11:01 Go to previous messageGo to next message
cookiemonster
Messages: 11279
Registered: September 2008
Location: Rainy Manchester
Senior Member
Technical or not, for people who understand english FROM ... WHERE ... AND ... has an entirely different logical meaning to FROM ... AND ...
Plus you haven't considered JOIN clauses.
Re: Why not remove the word "where" from the SQL statement [message #614452 is a reply to message #614420] Wed, 21 May 2014 13:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
Without WHERE, it would make generating dynamic statements easier, since each clause could just begin with AND. However, you do need the WHERE, so that you can tell whether the code following AND is part of a WHERE clause or JOIN condition or whatever. Perhaps, instead, it would be simpler to have multiple WHERE's and no AND's.
Re: Why not remove the word "where" from the SQL statement [message #614453 is a reply to message #614452] Wed, 21 May 2014 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59974
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Without WHERE, it would make generating dynamic statements easier, since each clause could just begin with AND.


This is currently easily gotten round starting the WHERE clause by "WHERE 1=1" and adding all conditions with an AND.

Re: Why not remove the word "where" from the SQL statement [message #614454 is a reply to message #614452] Wed, 21 May 2014 13:45 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
Hi, BB - I've missed your posts recently.

Back to the topic:

If we are going to re-write the ANSI SQL standard, I would like to replace SELECT with PROJECT. For example,

PROJECT ENAME,DNAME FROM EMP NATURAL JOIN DEPT WHERE SAL < 3000;

Rows are selected, columns are projected. I get irritated when people talk about "selecting columns", which shows you that, in Freudian terms, I must be a very anally retentive person.
Re: Why not remove the word "where" from the SQL statement [message #614457 is a reply to message #614454] Wed, 21 May 2014 15:34 Go to previous messageGo to next message
ThomasG
Messages: 3121
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I think where is not only "English" but also "Logically necessary".

(And as an old electrician I come from a "ladder logic" background, where I first got a grounding in the differences between AND and OR. I still think of the "select part" as somewhat an equivalent of the "coil" and the "where part" as something like the "switches")

./fa/1601/0/

Just as starting off with an AND would mean a dangling wire somewhere, starting a logical expression with the AND operand and not operator before it feels really wrong somehow. Even schoolchildren find 1+1=2 easier to grasp than +1+1=2. The second one only makes sense when you have a "previously calculated result".

In the "automatically generated where clause" realm it still is dangerous I think. It might work for simpler examples where you only have ANDs if you just start with an AND, but if it gets more complicated and you want to combine two separately generated where clauses you still need to know whether they "as a whole" need to be combined either with an AND or an OR.

Re: Why not remove the word "where" from the SQL statement [message #614471 is a reply to message #614454] Thu, 22 May 2014 01:08 Go to previous message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
John Watson wrote on Thu, 22 May 2014 00:15

If we are going to re-write the ANSI SQL standard, I would like to replace SELECT with PROJECT. For example,

PROJECT ENAME,DNAME FROM EMP NATURAL JOIN DEPT WHERE SAL < 3000;


I second you. And I have some more thoughts.

With the filter predicate, it is more appropriate and meaningful to have SELECT. Since we are selecting the rows based on a condition. Had it been without a condition, then I vote for you. "PROJECT columns FROM table" is more meaningful as it is simply projection of columns.

Now that would be a different discussion altogether whether we could have both the keywords based on the operation. We could write PROJECT when we are sure not to have any condition based selection. And if someone puts a WHERE clause it must throw an error "Filter predicate not allowed with PROJECT" or something like that.

And guess what, it would also make the developers use the ANSI syntax more and more. Because, when the PROJECT keyword won't allow the WHERE clause, it would be impossible to join the tables using old Oracle join syntax. So I believe we must have SELECT and PROJECT both and should be used as appropriate keywords based on the operation(selection of rows or projection of columns).
Previous Topic: DB-Engines Ranking: Oracle RDBMS the most popular product - 04.2014
Next Topic: New York, 3 June
Goto Forum:
  


Current Time: Thu Dec 18 03:02:38 CST 2014

Total time taken to generate the page: 0.14121 seconds