What is the difference, in the syntax, of the following databases, for a simple query, of type:
SELECT * FROM tabela WHERE id = '1' ORDER BY nome GROUP BY nome LIMIT 1
Or, what do they differ in syntax in general? Or is it all the same?
You do not need to explain the syntax of each bank. A general catch would be great, just to have a notion.
There is something called SQL ANSI .
ANSI is a standardization entity in the United States, equivalent to our ABNT.
There is actually an entity called ISO that is the worldwide standardization entity. In general, worldwide standardizations are submitted by national or regional entities and if approved by the body of the other standard- ers becomes a standard that everyone should follow.
Although we commonly call SQL ANSI, SQL is standardized by ISO as well. So any product should faithfully follow what the standard says. It would be better to call standard SQL.
In many languages this occurs. In SQL it never occurred.
The fact is that there is no product that implements the entire modern SQL standard. There is even one who does this based on an old standardization, for example, SQL-92 . And obviously several prostitutes put stuff that does not exist in the pattern for their product to stand out. The most recent when I wrote this response was the SQL: 2016 .
This becomes a problem because one day a form is standardized and this product can no longer conform to the standard.
Some products have never really hit the mark. They never thought this was important. It's actually good for them because it holds the customer in their product. Some think that it is they who should dictate what the pattern should have. Some cheat by providing the syntax that does nothing.
There is difficulty implementing certain SQL features. In some the problem is the interpretation of what is in the pattern. Although the syntax may be the same, the semantics are not. There are cases where the product does not do well with the standard.
For the basics everything works the same, at least in the syntax, if it were not so the product would not have the advantage of saying that it uses SQL and that you learning the language already knows how to use it.
In this case, SELECT , FROM , WHERE , ORDER BY and GROUP BY are same patterns. LIMIT is respected by almost all products. But you have a product that does not allow you to limit or use other syntax, the most obvious case is SQL Server that uses TOP as a keyword. It has product that extends the syntax of LIMIT .
Actually the syntax is less. Different semantics, how to optimize, and other details of the product has much more importance. The same syntax may give different results or have absurdly different performance on different products.
So it's silly these frameworks that try to abstract out the various SGDBs, especially those who try to do this when they accept very different philosophies. Resolving the syntax is easy, but this is not the most important.SQL is one of the most badly created and driven things in computing, like everything created by committees, one of the reasons no one can fully conform.
Note that SQL is not a programming language , at least not in its original basic form. With some extensions to it it can make the whole be a programming language and many products do this at least for their procedures . Actually already have shown that it is possible even the most modern standard SQL to be a programming language , but it depends on the implementation and it has to do crazy things to get the result.
There is a difference. It is worth searching the user manual that they make available on the web.
In SQL Server the referenced query looks as follows. SELECT TOP 1 * FROM table
that is, the limit does not go where the TOP goes in the select with the number of rows you want to return