Sql Table / Sub-query Alias Conventions

I've been writing SQL for a number of years now on various DBMS (Oracle, SQL Server, MySQL, Access etc.) and one thing that has always struck me is the seemingly lack of naming con

Solution 1:

DT1 and DT2 seems to be good approach .. I am in phase of understanding the existing Procedures and some Procedures use this naming convention DT1,DT2.. It becomes fairly simple to understand ..rather than giving some table short name as alias

Solution 2:

If I was using SQL Server I'd probably put the derived table in a Common Table Expression (CTE) with a logical name (to indicate what the table is) then shorten it with a correlation name ("table alias") in the main query (to aid readability) e.g.

WITH StockTransactions__type_S_or_B__smallest_units
     AS
     (
      <derived table query here>
     )
SELECT stkTrans.StockName
       ...
FROM tblStockTransactions AS stkTrans 
INNER JOIN StockTransactions__type_S_or_B__smallest_units AS stkTrans1 
   ON (stkTrans.BookCode = stkTrans1.BookCode) AND (stkTrans.Sedol = stkTrans1.Sedol)
GROUPBY stkTrans.BookCode, stkTrans.StockName, stkTrans.Sedol;

Obviously, this isn't an option in Access so you go straight to the correlation name and lose the full name entirely. This is not ideal but acceptable, IMO.

---

SQL requires a name to be assigned to a derived table for no reason at all. This example from Hugh Darwen, from which I think we can safely assume that the obligation annoys him:

SELECTDISTINCT E#, TOTAL_PAY 
FROM ( SELECT E#, SALARY + BONUS AS TOTAL_PAY
FROM EMP ) AS TEETH_GNASHER
WHERE TOTAL_PAY >= 500

Personally, for such a meaningless requirement I choose the almost meaningless and uncontroversial name, DT1 being a contraction of first derived table and allowing for DT2, DT3, etc e.g.

SELECTDISTINCT E#, TOTAL_PAY 
FROM ( SELECT E#, SALARY + BONUS AS TOTAL_PAY
FROM EMP ) AS DT1
WHERE TOTAL_PAY >= 500