SQL ANSI-92 표준이 ANSI-89보다 더 잘 채택되지 않는 이유는 무엇입니까?

SQL ANSI-92 표준이 ANSI-89보다 더 잘 채택되지 않는 이유는 무엇입니까?

---

내가 일한 모든 회사에서 사람들이 여전히 ANSI-89 표준으로 SQL 쿼리를 작성하고 있음을 발견했습니다.

select a.id, b.id, b.address_1
from person a, address b
where a.id = b.id

ANSI-92 표준 대신 :

select a.id, b.id, b.address_1
from person a
inner join address b
on a.id = b.id

이와 같은 매우 간단한 쿼리의 경우 가독성에 큰 차이가 없지만 큰 쿼리의 경우 조인 기준을 테이블에 나열하여 그룹화하면 조인에서 문제가 발생할 수있는 위치를 훨씬 쉽게 확인할 수 있습니다. WHERE 절에 모든 필터링을 유지하겠습니다. Oracle의 (+) 구문보다 외부 조인이 훨씬 직관적이라고 느낍니다.

내가 ANSI-92를 사람들에게 전파하려고 할 때 ANSI-89보다 ANSI-92를 사용하면 구체적인 성능상의 이점이 있습니까? 내가 직접 시도 해보 겠지만 여기에있는 Oracle 설정으로는 EXPLAIN PLAN을 사용할 수 없습니다. 사람들이 코드를 최적화하는 것을 원하지 않을까요?

---

Peter Gulutzan과 Trudy Pelzer의 "SQL 성능 조정"에 따르면 테스트 한 6 개 또는 8 개의 RDBMS 브랜드 중 SQL-89와 SQL-92 스타일 조인의 최적화 또는 성능에는 차이가 없었습니다. 대부분의 RDBMS 엔진은 쿼리를 최적화하거나 실행하기 전에 구문을 내부 표현으로 변환하므로 사람이 읽을 수있는 구문은 차이가 없다고 가정 할 수 있습니다.

또한 SQL-92 구문을 전파하려고합니다. 승인 된 지 16 년이 지난 지금은 사람들이 사용하기 시작할 때입니다! 그리고 이제 모든 브랜드의 SQL 데이터베이스가이를 지원하므로 혐오스러운 (+)Oracle 구문 또는 *=Microsoft / Sybase 구문 을 계속 사용할 이유가 없습니다 .

SQL-89 습관의 개발자 커뮤니티를 깨는 것이 왜 그렇게 어려운지에 관해서는 책, 잡지 기사의 고대 예를 사용하여 복사 및 붙여 넣기로 코딩하는 프로그래머로 구성된 대규모 "피라미드 기반"이 있다고 가정 할 수 있습니다. 또는 다른 코드 기반으로,이 사람들은 추상적으로 새로운 구문을 배우지 않습니다. 어떤 사람들은 패턴 매칭을하고 어떤 사람들은 암 기적으로 배웁니다.

하지만 점차적으로 SQL-92 구문을 사용하는 사람들이 예전보다 더 자주 보입니다. 저는 1994 년부터 온라인으로 SQL 질문에 답해 왔습니다.

---

ANSI092 표준에는 꽤 흉악한 구문이 포함되어 있습니다. 자연 조인 은 하나이고 USING 절은 다른 것입니다. IMHO, 테이블에 열을 추가해도 코드가 깨져서는 안되지만 NATURAL JOIN은 가장 심각한 방식으로 중단됩니다. 중단하는 "가장 좋은"방법은 컴파일 오류입니다. 예를 들어 당신이 * 곳을 선택하면, 열의 추가 할 수컴파일하지 못했습니다. 실패하는 다음으로 좋은 방법은 런타임 오류입니다. 사용자가 볼 수 있기 때문에 더 나쁘지만 여전히 무언가를 깨뜨렸다는 멋진 경고를 제공합니다. ANSI92를 사용하고 NATURAL 조인으로 쿼리를 작성하면 컴파일 타임에 중단되지 않고 런타임에 중단되지 않고 쿼리가 갑자기 잘못된 결과를 생성하기 시작합니다. 이러한 유형의 버그는 교활합니다. 보고서가 잘못되고 잠재적으로 재무 공개가 잘못되었습니다.

NATURAL 조인에 익숙하지 않은 사용자를위한 것입니다. 두 테이블에있는 모든 열 이름에서 두 테이블을 조인합니다. 4 열 키가 있고 입력하는 데 지쳤을 때 정말 멋집니다. 문제는 Table1에 DESCRIPTION이라는 기존 열이 있고 Table2에 새 열을 추가 할 때 발생합니다. 이름은 모르겠습니다. mmm, DESCRIPTION과 같은 무해한 것입니다. 이제 VARCHAR2에서 두 테이블을 조인합니다. (1000) 필드는 자유 형식입니다.

USING 절은 위에서 설명한 문제 외에도 전체 모호성을 유발할 수 있습니다. 다른 SO 게시물 에서 누군가이 ANSI-92 SQL을 보여주고 그것을 읽는 데 도움을 요청했습니다.

SELECT c.* 
FROM companies AS c 
JOIN users AS u USING(companyid) 
JOIN jobs AS j USING(userid) 
JOIN useraccounts AS us USING(userid) 
WHERE j.jobid = 123

이것은 완전히 모호합니다. 회사와 사용자 테이블 모두에 UserID 열을 넣었는데 불만이 없습니다. 회사의 UserID 열이 해당 행을 마지막으로 수정 한 사람의 ID이면 어떻게됩니까?

나는 진지합니다. 누구든지 그러한 모호성이 필요한 이유를 설명 할 수 있습니까? 표준에 바로 내장 된 이유는 무엇입니까?

코딩을 통해 거기에 복사 / 붙여 넣기하는 개발자 기반이 많다는 것이 Bill이 맞다고 생각합니다. 사실, 저는 ANSI-92에 관해서는 제가 그런 사람이라는 것을 인정할 수 있습니다. 내가 본 모든 예는 괄호 안에 중첩되는 여러 조인을 보여줍니다. 정직, 그것은 SQL에서 테이블을 고르는 것을 어렵게 만듭니다. 그러나 SQL92 evangilist는 실제로 조인 순서를 강제 할 것이라고 설명했습니다. JESUS ​​... 내가 본 모든 Copy Paster는 이제 실제로 조인 순서를 강제하고 있습니다.이 작업은 옵티 마이저, 특히 copy / paster 에게 남은 시간의 95 %가 더 좋습니다 .

토 말락이 말했을 때 바로 잡았습니다.

사람들은 새로운 구문으로 전환하지 않습니다.

그것은 나에게 무언가를 주어야하고 나는 상승세를 보지 못한다. 그리고 만약 긍정적 인면이 있다면, 부정적인 것은 무시하기에는 너무 큰 앨버트로스입니다.

---

몇 가지 이유가 떠 오릅니다.

• 사람들은 습관으로 그것을한다
• 사람들은 게으르고 타이핑이 적기 때문에 "이전 스타일"조인을 선호합니다.
• 초보자는 종종 SQL-92 조인 구문으로 머리를 감싸는 데 문제가 있습니다.
• 사람들은 새로운 구문으로 전환하지 않습니다.
• 사람들은 새로운 구문이 갖는 이점을 알지 못합니다. 주로 외부 조인을 수행 하기 전에 테이블을 필터링 할 수 있으며 WHERE 절만 있으면 테이블을 필터링 할 수 있습니다 .

필자는 모든 조인을 SQL-92 구문으로 수행하고 가능한 한 코드를 변환합니다. 더 깨끗하고 읽기 쉽고 강력한 방법입니다. 그러나 쿼리 결과를 변경하지 않고 더 많은 타이핑 작업으로 인해 상처를 입었다 고 생각할 때 누군가가 새로운 스타일을 사용하도록 설득하기는 어렵습니다.

---

위의 NATURAL JOIN 및 USING 게시물에 대한 응답입니다.

왜 이것을 사용해야 할 필요가 있는지 알 수 있습니까? ANSI-89에서는 사용할 수 없으며 바로 가기로 만 볼 수있는 ANSI-92 용으로 추가되었습니다.

나는 결코 조인을 기회에 남기지 않고 항상 테이블 / 별칭 및 ID를 지정합니다.

나에게 유일한 방법은 ANSI-92입니다. 더 장황하고 구문은 ANSI-89 추종자들이 좋아하지 않지만 JOINS와 FILTERING을 깔끔하게 분리합니다.

---

First let me say that in SQL Server the outer join syntax (*=) does not give correct results all the time. There are times when it interprets that as a cross join and not an outer join. So right there is a good reason to stop using it. And that outer join syntax is a deprecated feature and will not be in the next version of SQL Server after SQL Server 2008. You'll still be able to do the inner joins but why on earth would anyone want to? They are unclear and much much harder to maintain. You don't easily know what is part of the join and what is really just the where clause.

One reason why I believe you should not use the old syntax is that understanding joins and what they do and do not do is a critical step for anyone who will write SQL code. You should not write any SQL code without understanding joins thoroughly. If you understand them well, you will probably come to the conclusion that the ANSI-92 syntax is clearer and easier to maintain. I've never met a SQL expert who didn't use the ANSI-92 syntax in preference to the old syntax.

Most people who I have met or dealt with who use the old code, truly don't understand joins and thus get into trouble when querying the database. This is my personal experience so I'm not saying it is always true. But as a data specialist, I've had to fix too much of this junk through the years not to believe it.

---

I was taught ANSI-89 in school and worked in industry for a few years. Then I left the fabulous world of DBMS for 8 years. But then I came back and this new ANSI 92 stuff was being taught. I have learned the Join On syntax and now I actually teach SQL and I recommend the new JOIN ON syntax.

But the downside that I see is correlated subqueries don't seem to make sense in the light of ANSI 92 joins. When join information was included in the WHERE and correlated subqueries are "joined" in the WHERE all seemed right and consistent. In ANSI 92 table join criteria is not in the WHERE and subquery "join" is, the syntax seems inconsistent. On the other hand, trying to "fix" this inconsistency would probably just make it worse.

---

I don't know the answer for sure.. this is a religous war (albiet of a lesser degree than Mac-Pc or others)

A guess is that until fairly recently, Oracle, (and maybe other vendors as well) did not adopt the ANSI-92 standard (I think it was in Oracle v9, or thereabouts) and so, for DBAs/Db Developers working at companies which were still using these versions, (or wanted code to be portable across servers that might be using these versions, they had to stick to the old standard...

It's a shame really, because the new join syntax is much more readable, and the old syntax generates wrong (incorrect) results in several well-documented scenarios.

• Specifically, outer Joins when there are conditional filtering predicates on non-Join related columns from the table on the "outer" side of the join.

---

Inertia and practicality.

ANSI-92 SQL is like touch-typing. In some theoretical way it might make everything better someday, but I can type much faster looking at the keys with four fingers now. I would need to go backwards in order to go forwards, with no guarantee that there would ever be a pay-off.

Writing SQL is about 10% of my job. If I need ANSI-92 SQL to solve a problem that ANSI-89 SQL can't solve then I'll use it. (I use it in Access, in fact.) If using it all the time would help me solve my existing problems much faster, I'd spend the time to assimilate it. But I can whip out ANSI-89 SQL without ever thinking about the syntax. I get paid to solve problems--thinking about SQL syntax is a waste of my time and of my employer's money.

Someday, young Grasshopper, you'll be defending your use of ANSI-92 SQL syntax against young people whining that you should be using SQL3 (or whatever). And then you'll understand. :-)

---

I had a query that was originally written for SQL Server 6.5, which did not support the SQL 92 join syntax, i.e.

select foo.baz
from foo
  left outer join bar
  on foo.a = bar.a

was instead written as

select foo.baz
from foo, bar
where foo.a *= bar.a

The query had been around for a while, and the relevant data had accumulated to make the query run too slow, abut 90 seconds to complete. By the time this problem arose, we had upgraded to SQL Server 7.

After mucking about with indexes and other Easter-egging, I changed the join syntax to be SQL 92 compliant. The query time dropped to 3 seconds.

There's a good reason to switch.

Reposted from here.

---

I can answer from the point of view of an average developer, knowing just enough SQL to understand both syntaxes, but still googling the exact syntax of insert each time I need it... :-P (I don't do SQL all day, just fixing some problems from time to time.)

Well, actually, I find the first form more intuitive, making no apparent hierarchy between the two tables. The fact I learned SQL with possibly old books, showing the first form, probably doesn't help... ;-)
And the first reference I find on a sql select search in Google (which returns mostly French answers for me...) first shows the older form (then explain the second one).

Just giving some hints on the "why" question... ^_^ I should read a good, modern book (DB agnostic) on the topic. If somebody has suggestions...

---

I can't speak for all schools but at my university when we were doing the SQL module of our course, they didn't teach ANSI-92, they taught ANSI-89 - on an old VAX system at that! I wasn't exposed to ANSI-92 until I started digging around in Access having built some queries using the query designer and then digging into the SQL code. Realising I had no idea how it was completing the joins, or the implications of the syntax I started digging deeper so I could understand it.

Given that the available documentation isn't exactly intuitive in a lot of cases, and that people tend to stick to what they know and in many cases don't strive to learn any more than they need in order to get their job done, it's easy to see why adoption is taking so long.

Of course, there are those technical evangelists that like to tinker and understand and it tends to be those types that adopt the "newer" principles and try to convert the rest.

Oddly, it seems to me that a lot of programmers come out of school and stop advancing; thinking that because this is what they were taught, this is how it's done. It's not until you take off your blinkers that you realise that school was only meant to teach you the basics and give you enough understanding to learn the rest yourself and that really you barely scratched the surface of what there is to know; now it's your job to continue that path.

Of course, that's just my opinion based on my experience.

---

1) Standard way to write OUTER JOIN, versus *= or (+)=

2) NATURAL JOIN

3) Depend in the database engine, ANSI-92 trends to be more optimal.

4) Manual optimization :

Let's say that we have the next syntax (ANSI-89):

(1)select * from TABLE_OFFICES to,BIG_TABLE_USERS btu
where to.iduser=tbu.iduser and to.idoffice=1

It could be written as:

(2)select * from TABLE_OFFICES to
inner join BIG_TABLE_USERS btu on to.iduser=tbu.iduser
where to.idoffice=1

But also as :

(3)select * from TABLE_OFFICES to
inner join BIG_TABLE_USERS btu on to.iduser=tbu.iduser and to.idoffice=1

All of them (1),(2),(3) return the same result, however they are optimized differently, it depends in the database engine but most of them do :

• (1) its up to the database engine decide the optimization.
• (2) it joins both tables then do the filter per office.
• (3) it filters the BIG_TABLE_USERS using the idoffice then join both tables.

5) Longer queries are less messy.

---

Reasons people use ANSI-89 from my practical experience with old and young programmers and trainees and fresh graduates:

• They learn SQL from existing code they see (rather than books) and learn ANSI-89 from code
• ANSI-89 because is less typing
• They do not think about it and use one or other style and do not even know which of both is considered new or old and do not care either
• The idea that code is also a communication to the next programmer coming along maintaining the code does not exist. They think they talk to the computer and the computer does not care.
• The art of "clean coding" is unknown
• Knowledge of programming language and SQL specifically is so poor that they copy and paste together what they find elsewhere
• Personal preference

I personally prefer ANSI-92 and change every query I see in ANSI-89 syntax sometimes only to better understand the SQL Statement at hand. But I realized that the majority of people I work with are not skilled enough to write joins over many tables. They code as good as they can and use what they memorized the first time they encountered a SQL statement.

---

Here are a few points comparing SQL-89, and SQL-92 and clearing up some misconceptions in other answers.

1. NATURAL JOINS are a horrible idea. They're implicit and they require meta-information about the table. Nothing about SQL-92 requires their use so simply ignore them. They're not relevant to this discussion.
2. USING is a great idea, it has two effects:
   1. It produces only one column on the result set from an equijoin.
   2. It's enforces a sound and sane convention. In SQL-89 you had people writing the column id on both tables. After you join the tables, this becomes and ambiguous and it requires explicit aliasing. Further, the ids on the join almost certainly had different data. If you join person to company, you now have to alias one id to person_id, and one id to company_id, without which the join would produce two ambiguous columns. Using a globally-unique identifier for the table's surrogate key is the convention the standard rewards with USING.
3. The SQL-89 syntax is an implicit CROSS JOIN. A CROSS JOIN doesn't reduce the set, it implicitly grows it. FROM T1,T2 is the same as FROM T1 CROSS JOIN T2, that produces a Cartesian join which is usually not what you want. Having the selectivity to reduce that removed to a distant WHERE conditional means that you're more likely to make mistakes during design.
4. SQL-89 , and SQL-92 explicit JOINs have different precedence. JOIN has a higher precedence. Even worse, some databases like MySQL got this wrong for a very long time.. So mixing the two styles is a bad idea, and the far more popular style today is the SQL-92 style.

---

Oracle does not implement ANSI-92 at all well. I've had several problems, not least because the data tables in Oracle Apps are so very well endowed with columns. If the number of columns in your joins exceeds about 1050 columns (which is very easy to do in Apps), then you will get this spurious error which makes absolutely no logical sense:

ORA-01445: cannot select ROWID from a join view without a key-preserved table.

Re-writing the query to use old style join syntax makes the issue disappear, which seems to point the finger of blame squarely at the implementation of ANSI-92 joins.

Until I encountered this problem, I was a steadfast promoter of ASNI-92, because of the benefits in reducing the chance of an accidental cross join, which is far too easy to do with old-style syntax.

Now, however, I find it much more difficult to insist on it. They point to Oracle's bad implementation and say "We'll do it our way, thanks."

---

A new SQL standard inherits everything from the previous standard, a.k.a. 'the shackles of compatibility'. So the 'old' / 'comma-separated' / 'unqualified' join style is perfectly valid SQL-92 sytax.

Now, I argue that SQL-92's NATURAL JOIN is the only join you need. For example, I argue it is superior to inner join because it does not generate duplicate columns - no more range variables in SELECT clauses to disambiguate columns! But I can't expected to change every heart and mind, so I need to work with coders who will continue to adopt what I personally consider to be legacy join styles (and they may even refer to range variables as 'aliases'!). This is the nature of teamwork and not operating in a vacuum.

One of the criticisms of the SQL language is that the same result can be obtained using a number of semantically-equivalent syntaxes (some using relational algebra, some using the relational calculus), where choosing the 'best' one simply comes down to personal style. So I'm as comfortable with the 'old-style' joins as I am with INNER. Whether I'd take the time to rewrite them as NATURAL depends on context.

참고URL : https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89