Understanding the past from the two SQL table Connection Methods

For example, a two-table join SQL statement can be written in either of the following ways:
(1) select A. c1, A. c2, B. c1, B. c2
From table1 A, table2 B
Where A. id = B. id

(2) select A. c1, A. c2, B. c1, B. c2
From table1 A join table2 B
On A. id = B. id

Which method is better? Which one do you advocate?
Which one do you like to use?
Copy codeThe Code is as follows:
Select * from a, B where a. id = B. id
Select * from a inner join B on a. id = B. id


--- Which of the two is better?

The answer on the 11 th floor is the most in-depth. In fact, there are still some historical reasons for this problem. No matter what kind of writing you are used to, as long as you know the ins and outs, you will not be confused by the details. I personally know the following points. If you have any deviations, please correct them.

To put it simply, the former is the ansi SQL 86 standard and the latter is the ansi SQL 92 standard (***), which is the easiest way to accept.

What is ansi? The US National Bureau of Standards, one of the important iso members, was available in 1918.
What is ansi SQL? That is, ansi has noticed the SQL productivity, so it has been normalized.

What is SQL? He was invented by ibm and has a wide range of oracle languages.

Why are they two companies ?.
At the beginning of 1970s, due to the fierce internal interest struggles of ibm, the research results of a certain ox could only be published in the form of a paper.
At the end of 1970s, a small company used this technology in the commercial field to become oracle, and it was not until n years later that ibm db2 came out.

Therefore, SQL is not invented by ansi, and ansi standards cannot be used by all database platforms.

For example, what is the database syntax? Anyway, the ansi standard reports an error in it.
Select * from (a inner join B on a. id = B. id) inner join c on a. id = c. id

So what are the databases before ansi86? Oracle and db2. There are also some small roles at that time: Informix, dbase series and so on.

Sybase's database and SQLServer came out 86 years later, while the previous strange join syntax's access was 90 years later.

I have never used the old sqlserver and oracle databases. Anyway, when I used oracle8i in, I still did not support the inner join of ansi 92. He was born in 97 years. The oacle9i was released this century. If you use t-SQL, you will be asked about left join, where. id = B. id (+) is enough. It is not so stupid. t-SQL still has the expression "* =" before.


So it seems that the ansi specification is not strong enough? In fact, no, international standardization cannot be achieved in a one-size-fits-all manner. ansi92 defines four levels and n multiple terms. The general idea is that everyone meets the entry level. Other high levels are for reference only, and even iso won't verify other levels at all .. for example, inner join and left join are all transitional.


So the words I typed five stars earlier are not completely correct. The correct one should be:

The former complies with the ansi 86 standard and the ansi 92 entry-level standard, and the latter complies with the ansi92 transition level standard.

It is not that oracle8i does not comply with ansi92, but does not comply with ansi92's high-level specifications, and database systems that fully implement high-level standards do not.

As early as oracle7, it already fully complies with ansi92. Of course, it refers to the entry level, and it is also an ansi92 template example.

-- Back to the above topic, which of the two is better?
Of course, the performance is exactly the same. The difference is just habits and preferences, but it also has different risks due to different standard levels.

If you want code that is elegant, easy to maintain, and not easy to write wrong, use the second method with high standards.
If you need to consider the risk factor, such as migration or integration involving multiple platforms, you should use the first one. At least two tables are safe.

----
By the way, the ansi standard has been being revised: we will not list the specific versions, but we will naturally get a lot of benefits, for example, recursion, objects, arrays, xml, and so on are gradually implemented in the new versions of mainstream databases.