Geek asks and answers

When is it required to specify a table name as an alias in SQL?

I noticed that when executing a query with multiple JOINs, my query did not work unless I gave one of the table names an alias.

Here is a simple example to explain the point:

This one does not work :

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases on items.date=purchases.purchase_date
group by folder_id

This one does :

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases as p on items.date=p.purchase_date
group by folder_id

Can someone explain this?

+5
source share
6 answers

The request uses the same shopping table. You must distinguish them by a different name.

You need to specify an alias:

  • When the same table name is referenced multiple times

, . , . , , . , , SQL , . , , SQL- .

Script 1: t1 t2

SELECT      t1.col2
FROM        table1 t1
INNER JOIN  table1 t2
ON          t1.col1 = t2.col1
  • When there is a derived table/sub query output

, , , , . , -, - SQL, , . , , SQL- .

Script 2: t1 - .

SELECT col1
FROM
(
    SELECT col1
    FROM   table1
) t1
+5

, , - (, ) (, Siva). , , .

, :

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases on items.date=purchases.purchase_date
group by folder_id

, , join on , . , , on items.date=purchases.purchase_date, SQL , .

, , . SQL 100% , . , , , .

+4

, . , .purchase_date.

+1

, , SQL- , .

? :

SELECT 
    subject
from 
    items
    join purchases 
        on items.folder_id=purchases.item_id
        and items.date=purchases.purchase_date
group by folder_id
+1

, .

, , , coulmn.

, , , .

, " " (), , . .

, , . , .

+1

, , - , . . , qualify table.field alias.field, , .

, , , . - , , , , S/O. , , , .

select
      SomeField,
      AnotherField
   from
      OneOfMyTables
         Join SecondTable
            on SomeID = SecondID


select
      T1.SomeField,
      T2.AnotherField
   from
      OneOfMyTables T1
         JOIN SecondTable T2
            on T1.SomeID = T2.SecondID

In these two scenarios that you prefer to read ... Note: I simplified the query using the shorter aliases "T1" and "T2", but they can be any, even abbreviated or abbreviated aliases of the table names ... "oomt" (one of my tables) and "st" (second table). Or, as something very long, as it was in other posts ...

Select * from ContractPurchaseOffice_AgencyLookupTable

vs 

Select * from ContractPurchaseOffice_AgencyLookupTable  AgencyLkup

If you needed to save qualification associations or field columns that you would prefer to see.

Hope this clarifies your question.

0
source

More articles:


All Articles