Syntax difference between databases

Question

What is the difference, in the syntax, of the following databases, for a simple query, of the type:

SELECT * FROM tabela WHERE id = '1' ORDER BY nome GROUP BY nome LIMIT 1

Or, what do they differ from each other in syntax in general? Or is it all the same?

Benches:

• Oracle
• Mysql
• SQL Server
• Postgresql

No need to explain the syntax of each database. One caught general would be great, just to get a sense.

Answer 1

There’s something called SQL ANSI.

ANSI is a standardization entity of the United States, equivalent to our ABNT.

In fact there is an entity called ISO that is the world standardizing entity. In general world standardizations are submitted by national or macro regional entities and if approved by the body of other standardizers becomes a standard that all should follow.

Although we commonly call SQL ANSI, SQL is standardized by ISO as well. So any product should follow faithfully what the standard says. It would be better to call SQL standard.

In many languages this occurs. In SQL it never occurred.

The fact is that there is no product that implements all the modern SQL standard. There are even those who do this based on an old standardization, the SQL-92 for example. And obviously several products put things that don’t exist in the standard for your product to stand out. The most recent when I wrote this reply was SQL:2016.

This becomes a problem because one day a shape is standardized and this product can no longer conform to the standard.

Some products never really cared about the pattern. They never thought it mattered. It’s actually good for them because it traps the customer in their product. Some think they should dictate what the standard should have. Some cheat by providing syntax that does nothing.

There is difficulty in 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 is not. There are cases that the product does not behave well with the pattern.

For the basic everything works the same, at least in the syntax, if it were not so the product would not have the advantage of saying that uses SQL and that you learn the language already know how to use it.

In this case SELECT, FROM, WHERE, ORDER BY and GROUP BY are really patterns. LIMIT is respected by almost all products. But has product that does not allow to limit or uses other syntax, the most obvious case is SQL Server that uses TOP as keyword. It has product that extends the syntax of LIMIT.

Actually the syntax is the least. The different semantics, the way to optimize, and other details of the product have much more importance. The same syntax can give different results or have absurdly different performance in different products.

That’s why it’s so silly frameworks who try to abstract the various Sgdbs, especially those who try to do this when they accept very different philosophies. Solving the syntax is easy, but this is not the most important thing.

SQL is one of the most poorly created and driven things in computing, like everything that is created by committees, one of the reasons nobody conforms integrally.

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 a programming language and many products do this at least for their procedures. Actually already have shown that it is possible even more modern standard SQL to be a programming language, but it depends on the implementation and has to do crazy things to get the result.

Answer 2

There is a difference. It is worth searching in the user manual that make available on the web.

In SQL Server the query mentioned is as follows. SELECT TOP 1 * FROM table

ie the limit does not go in Where the TOP goes in select with the amount of lines you want to return