-1

I am working on the question, and after checking the answer posted by others on the internet, I can't understand why is there 'From albums a, artists ar' instead of 'From albums, artists'?

What does the 'a' and 'ar' stand for?

*I tried to correct my syntax, the original post is in the picture. After seeing your comments I know those are aliases, thanks a lot

select count(*) from (select *
from artists
left join albums
on artists.ArtistId=albums.ArtistId)
where Name="Led Zeppelin";

The question is：enter image description here

some scary cereal killer
  • 3
  • 3
  • Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (**without errors**), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Mar 18 '22 at 07:40
  • BTW, the above query is invalid and expected to raise an error. (GROUP BY missing.) – jarlh Mar 18 '22 at 07:41
  • @jarlh this won’t raise an error in some versions of MySQL (and valid according to SQL:1999 and above). In this case the column is implicitly treated as `any_value(column)`, which in this case looks to be acceptable because of the filtering on `artistId` (assuming `artist.name` is unique) – Andrew Sayer Mar 18 '22 at 07:58
  • @AndrewSayer, seems like OP is using SQLite - with its very "relaxed" group by rules. Also, note the implicit cross join. – jarlh Mar 18 '22 at 08:40
  • @jarlh I totally missed the lack of join condition, I take back my “this looks fine” :) – Andrew Sayer Mar 18 '22 at 09:19
  • @jarlh sir, should I change like this to make it more explicit : `select count(*) from (select * from artists left join albums on artists.ArtistId=albums.ArtistId) where Name="Led Zeppelin";` And after I inserted this into the answer sheet, the page returned me a one-line data for me to spot the answer to the next blank-filling question. So it seems that I don't need to use GROUP BY in this assignment – some scary cereal killer Mar 19 '22 at 13:06

2 Answers2

1

It is an alias you can use an alias when you join to access its corresponding columns. It could be anything. If you do not specify the alias and tries to access the column name present in both the table with same name give you ambiguity error

Like this

Select Count(Distinct albs.AlbumId) AS album_number, albs.ArtistId, arts.Name
From albums albs, artists arts
Where a.ArtistId in (Select ar.ArtistId
                     From artists ar
                     Where ar.Name = 'Led Zeppelin')
Nayanish Damania
  • 542
  • 5
  • 13
1

These are aliases for datasets or tables named before. So a is albums table and ar is artists in the shown context. SQL allows to skip AS before such aliases. Technically the complete statement sounds like this: FROM albums AS a, artists AS ar. By the way comma is also shugar for JOIN (see here). Aliases can also be used to substitute names of columns in the dataset.

asd-tm
  • 3,381
  • 2
  • 24
  • 41