SQL 키워드“AS”의 목적은 무엇입니까?

SQL 키워드“AS”의 목적은 무엇입니까?

---

테이블 이름 바로 뒤에 식별자를 입력하여 SQL에서 테이블 별칭을 설정할 수 있습니다.

SELECT * FROM table t1;

AS별칭을 나타 내기 위해 키워드 를 사용할 수도 있습니다 .

SELECT * FROM table AS t1;

그들 사이의 차이점은 무엇입니까?

나는 오래된 DBA 사람들이없이 문을 작성하는 경향이 AS있지만 대부분의 새로운 자습서에서이를 사용합니다.

업데이트 : 테이블 및 열 별칭의 목적이 무엇인지 알고 있습니다. 별명없이 별명을 설정하는 별도의 키워드가 필요한 이유가 궁금합니다.

---

위의 두 진술 사이에는 차이가 없습니다. AS는 별칭을 언급하는 더 명확한 방법입니다.

---

내 앞에 응답 한 사람은 모두 맞습니다. 조인이있는 긴 쿼리 나 쿼리가있는 경우 테이블의 별칭 바로 가기 이름으로 사용합니다. 다음은 몇 가지 예입니다.

실시 예 1

SELECT P.ProductName,
       P.ProductGroup,
       P.ProductRetailPrice
FROM   Products AS P

실시 예 2

SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Quantity
FROM   Products AS P
LEFT OUTER JOIN Orders AS O ON O.ProductID = P.ProductID
WHERE  O.OrderID = 123456

예 3 AS 키워드를 사용하는 것이 좋으며 매우 권장되지만 하나없이 동일한 쿼리를 수행 할 수 있습니다 (자주 수행하는 경우도 있음).

SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Quantity
FROM   Products P
LEFT OUTER JOIN Orders O ON O.ProductID = P.ProductID
WHERE  O.OrderID = 123456

알다시피, 마지막 예에서 AS 키워드를 생략했습니다. 별칭으로 사용할 수 있습니다.

실시 예 4

SELECT P.ProductName AS "Product",
       P.ProductRetailPrice AS "Retail Price",
       O.Quantity AS "Quantity Ordered"
FROM   Products P
LEFT OUTER JOIN Orders O ON O.ProductID = P.ProductID
WHERE  O.OrderID = 123456

실시 예 4의 출력

Product             Retail Price     Quantity Ordered
Blue Raspberry Gum  $10 pk/$50 Case  2 Cases
Twizzler            $5 pk/$25 Case   10 Cases

---

어떤 구문을 선택해야할지 확실하지 않은 경우, 특히 선택 사항을 구분할 것이 많지 않은 경우 휴리스틱에 관한 책을 참조하십시오. 내가 아는 한, SQL에 대한 유일한 휴리스틱 책은 'Joe Celko의 SQL 프로그래밍 스타일'입니다.

A correlation name is more often called an alias, but I will be formal. In SQL-92, they can have an optional AS operator, and it should be used to make it clear that something is being given a new name. [p16]

This way, if your team doesn't like the convention, you can blame Celko -- I know I do ;)

---

UPDATE 1: IIRC for a long time, Oracle did not support the AS (preceding correlation name) keyword, which may explain why some old timers don't use it habitually.

---

UPDATE 2: the term 'correlation name', although used by the SQL Standard, is inappropriate. The underlying concept is that of a ‘range variable’.

---

UPDATE 3: I just re-read what Celko wrote and he is wrong: the table is not being renamed! I now think:

A correlation name is more often called an alias, but I will be formal. In Standard SQL they can have an optional AS keyword but it should not be used because it may give the impression that something is being renamed when it is not. In fact, it should be omitted to enforce the point that it is a range variable.

---

The AS keyword is to give an ALIAS name to your database table or to table column. In your example, both statement are correct but there are circumstance where AS clause is needed (though the AS operator itself is optional), e.g.

SELECT salary * 2 AS "Double salary" FROM employee;

In this case, the Employee table has a salary column and we just want the double of the salary with a new name Double Salary.

Sorry if my explanation is not effective.

---

Update based on your comment, you're right, my previous statement was invalid. The only reason I can think of is that the AS clause has been in existence for long in the SQL world that it's been incorporated in nowadays RDMS for backward compatibility..

---

The use is more obvious if you don't use 'SELECT *' (which is a bad habit you should get out of):

SELECT t1.colA, t2.colB, t3.colC FROM alongtablename AS t1, anotherlongtablename AS t2, yetanotherlongtablename AS t3 WHERE t1.colD = t2.colE...

---

It's a formal way of specifying a correlation name for an entity so that you can address it easily in another part of the query.

---

The AS in this case is an optional keyword defined in ANSI SQL 92 to define a <<correlation name> ,commonly known as alias for a table.

<table reference> ::=
            <table name> [ [ AS ] <correlation name>
                [ <left paren> <derived column list> <right paren> ] ]
          | <derived table> [ AS ] <correlation name>
                [ <left paren> <derived column list> <right paren> ]
          | <joined table>

     <derived table> ::= <table subquery>

     <derived column list> ::= <column name list>

     <column name list> ::=
          <column name> [ { <comma> <column name> }... ]


     Syntax Rules

     1) A <correlation name> immediately contained in a <table refer-
        ence> TR is exposed by TR. A <table name> immediately contained
        in a <table reference> TR is exposed by TR if and only if TR
        does not specify a <correlation name>.

It seems a best practice NOT to use the AS keyword for table aliases as it is not supported by a number of commonly used databases.

---

In the early days of SQL, it was chosen as the solution to the problem of how to deal with duplicate column names (see below note).

To borrow a query from another answer:

SELECT P.ProductName,
       P.ProductRetailPrice,
       O.Quantity
  FROM Products AS P
       INNER JOIN Orders AS O ON O.ProductID = P.ProductID
 WHERE O.OrderID = 123456

The column ProductID (and possibly others) is common to both tables and since the join condition syntax requires reference to both, the 'dot qualification' provides disambiguation.

Of course, the better solution was to never have allowed duplicate column names in the first place! Happily, if you use the newer NATURAL JOIN syntax, the need for the range variables P and O goes away:

SELECT ProductName, ProductRetailPrice, Quantity
  FROM Products NATURAL JOIN Orders
 WHERE OrderID = 123456

But why is the AS keyword optional? My recollection from a personal discussion with a member of the SQL standard committee (either Joe Celko or Hugh Darwen) was that their recollection was that, at the time of defining the standard, one vendor's product (Microsoft's?) required its inclusion and another vendor's product (Oracle's?) required its omission, so the compromise chosen was to make it optional. I have no citation for this, you either believe me or not!

---

In the early days of the relational model, the cross product (or theta-join or equi-join) of relations whose headings are not disjoint appeared to produce a relation with two attributes of the same name; Codd's solution to this problem in his relational calculus was the use of dot qualification, which was later emulated in SQL (it was later realised that so-called natural join was primitive without loss; that is, natural join can replace all theta-joins and even cross product.)

Source: Business System 12, Notes keyed to slides of the presentation given at TTM Implementers’ Workshop, University of Northumbria, 2-3 June 2011 by Hugh Darwen

---

If you design query using the Query editor in SQL Server 2012 for example you would get this:

  SELECT        e.EmployeeID, s.CompanyName, o.ShipName
FROM            Employees AS e INNER JOIN
                         Orders AS o ON e.EmployeeID = o.EmployeeID INNER JOIN
                         Shippers AS s ON o.ShipVia = s.ShipperID
WHERE        (s.CompanyName = 'Federal Shipping')

However removing the AS does not make any difference as in the following:

 SELECT        e.EmployeeID, s.CompanyName, o.ShipName
FROM            Employees e INNER JOIN
                         Orders o ON e.EmployeeID = o.EmployeeID INNER JOIN
                         Shippers s ON o.ShipVia = s.ShipperID
WHERE        (s.CompanyName = 'Federal Shipping')

In this case use of AS is superfluous but in many other places it is needed.

참고URL : https://stackoverflow.com/questions/4164653/whats-the-purpose-of-sql-keyword-as