1

I am in the beginning stages of learning SQL through Google's Data Analytics course on Coursera. While I understand the syntax of temporarily renaming tables and columns with AS ([original name] AS [new name]), I'm not quite sure I understand the order placement. There is an example they provided in Big Query shown below.

1 SELECT
2  seasons.market AS university,
3  seasons.name AS team_name,
4  seasons.wins,
5  seasons.losses,
6  seasons.ties,
7  mascots.mascot AS team_mascot
8 FROM
9  `bigquery-public-data.ncaa_basketball.mbb_historical_teams_seasons` AS seasons
10 INNER JOIN
11  `bigquery-public-data.ncaa_basketball.mascots` AS mascots
12 ON
13  seasons.team_id = mascots.id
14 WHERE
15  seasons.season = 1984
16  AND seasons.division = 1
17 ORDER BY
18  seasons.market

Essentially, what is tripping me up is how line 2 can get the right "table.column" address for "seasons.market" when bigquery-public-data.ncaa_basketball.mbb_historical_teams_seasons is redefined as "seasons" in line 9.

With all of that said, my question is: is there an SQL function that exists simply to establish as alias? I understand that the above example works, but it is hard for me to wrap my head around a program reading lines 1,9,2,3... I would love to be able to start off my queries by plugging in relevant and concise names at the TOP, not the middle. Ideal situation shown below.

[ALIAS ESTABLISHMENT FUNCTION]
   reallylongtablename1 AS tbl1
SELECT
   exampleColumnA
FROM
   tbl1

Thank you very much!

TheTravdrum
  • 85
  • 6
  • Regarding the first question: [Why do “linq to sql” queries starts with the FROM keyword unlike regular SQL queries?](https://stackoverflow.com/questions/32668427/why-do-linq-to-sql-queries-starts-with-the-from-keyword-unlike-regular-sql-que) or [Logical SELECT clause order](https://www.jooq.org/doc/3.14/manual/sql-building/sql-statements/select-statement/select-lexical-vs-logical-order/) – Lukasz Szozda May 29 '21 at 17:32
  • 1
    @Lukasz Szozda, thank you! My first question when I started with SQL is why doesn't it go biggest to littlest FROM > SELECT > WHERE. I tried rearranging to this order and couldn't get the code to run. This will give me a little homework to dig into! – TheTravdrum Jun 02 '21 at 00:02

1 Answers1

0

You can use a CTE:

with tbl1 as (
      select *
      from reallylongtablename1
     )
select exampleColumnA
from tbl1;

More commonly this is used for complex queries, not just to shorten a table name.

As for your first question, that is a question of how the scoping rules for identifiers are defined in a SQL query. In a SELECT query, the FROM clause is used to define the table aliases and associated column names. These can these be used in the other clauses.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for the help, @Gordon Linoff! I will take some time to experiment with this. I believe this will be a great way for me to understand alias assignments. Would it be an oversimplification to say that the default for anything table-related falls under FROM? – TheTravdrum Jun 02 '21 at 00:06
  • @TheTravdrum . . . In a `SELECT` query all table references start in the `FROM` clause. – Gordon Linoff Jun 02 '21 at 00:56