2

I'm not really sure how to describe the question I'm asking because I'm not really sure what it is.

I'm a beginner in SQL and just learning in my free time. I've been looking at the w3schools and codeacademy course and I encounter this for the first time. I don't understand why we have used the letter a in either of these cases. I've looked at some other threads for advice but they use similar notation but different letters. Pics of the code below.

w3 schools self join

code academy union all

Can someone please explain what is the point of using the letter a and also when/what situations would you use this.

Cheers

Edit: So the first pic is an alias and the second is a subquery. I'm interested in the second pic where the subquery is named because don't quite understand it.

Edit 2: I've been reading more and more into aliasing from the provided links and still don't fully understand the syntax. This may just be because it's a self join and I'm confusing myself. In the first picture, there is only table named "Customer" so why is there "FROM Customers A, Customers B"? Database can be viewed here

Han
  • 35
  • 1
  • 8
  • Possible duplicate of [When to use SQL Table Alias](https://stackoverflow.com/questions/198196/when-to-use-sql-table-alias) – Phil Sep 13 '17 at 04:04
  • This is just alias when your query run successfully its result derived as "a" read following documents [1. W3 Schools](https://www.w3schools.com/sql/sql_alias.asp) [2. Tutorial points](https://www.tutorialspoint.com/sql/sql-alias-syntax.htm) [3. Do factory](http://www.dofactory.com/sql/alias) – Jitendra Solanki Sep 13 '17 at 04:09
  • Thanks guys, I couldn't find it because I didn't know what it was called. Only came back to SQL today after a few weeks break and did not remember the alias lesson. – Han Sep 13 '17 at 04:39

4 Answers4

1

In the first example, this is called a table alias, and it's used for a few reasons:

  1. To save time when writing a query. Many IDEs (Integrated Development Environments, or code editors such as SSMS, SQL Developer) will let you autocomplete a column name, and it's easier to do when you specify a table alias, instead of a full table name (e.g. "a" instead of "customers"). Even without the autocomplete, it's easier to type a.CustomerName instead of Customers.CustomerName.
  2. To specify which column a table belongs to when querying a table more than once, which is what your example uses. This is common when performing self-joins or checking for duplicates.

In your second example, this is called naming a subquery. It means that the part inside the brackets is referred to as "a", and can be treated like its own table or view in the query.

For example, instead of:

SELECT COUNT(*) FROM...

You could say:

SELECT COUNT(a.id) FROM ...

This is because "id" is a column from the subquery called "a".

Or, if you wanted to get a SUM of the sale_price column instead, you can refer to it as though it was a column of a table called "a":

SELECT SUM(a.sale_price) FROM ...
bbrumm
  • 1,342
  • 1
  • 8
  • 13
  • So both of these count the number of rows right? But why do we need FROM in the second one when from is already in a? I'm not sure if my question makes sense but hopefully you could explain this further. Cheers – Han Sep 13 '17 at 04:48
  • Actually the first example of SELECT COUNT(*) counts the number of rows in the subquery. The second example of SELECT SUM(a.sale_price) will find the SUM of all of the sale_price values. So if you had three records returned from the subquery with sale_price of 10, 20, and 5, then the COUNT will return 3 but the SUM will return 35. Does that answer your question? – bbrumm Sep 15 '17 at 04:38
  • Hi bbrumm, isn't a.sale_price also a count in this case? SELECT count(*) FROM(... ) is all a is it not? Could you explain it a bit further? Thanks, Han – Han Sep 19 '17 at 04:59
  • Han - yes you could say COUNT(a.sale_price). I think my answer was a bit confusing, I'll update it. – bbrumm Sep 19 '17 at 05:55
0

Those are alias for the table names and offer a way to write your SQL statement more succinctly by not having to type out the full table name again in other parts of the query. The letter A is arbitrary in these cases. Sometimes "t1", "t2", etc is used. Other times it is the first letter or couple of letters in the table name.

I am not sure of the reason of using "a" in your second example, however.

aakoch
  • 1,164
  • 1
  • 9
  • 18
  • In case 2, it is simply mandatory. Otherwise `ERROR 1248 (42000): Every derived table must have its own alias`. – Michael - sqlbot Sep 13 '17 at 20:36
  • Could you please explain why it's mandatory? I don't really understand the error. Why must every derived table have its own alias? – Han Sep 13 '17 at 23:40
0

This is known as aliasing in sql. We use aliasing because we don't need to write the whole table name in future, we can just write that alias. For example here the a represents the output of the query written in brackets. whenever we want to use this result we can simply write 'a' instead of writing the whole query again like in case we need to join this result with another table or specify in where clause etc.

0

In your first example, the letters are used to alias a table so that you don't have to write the full name of table again and again.

In your second example, the sub-query is given an alias. The only reason behind giving the alias to sub-query is that this is the way the syntax works in MySQL. If in the future, you need to apply a WHERE clause to that sub-query, the alias defined will come in handy.

So, in this case, the alias is redundant but needs to be added just to follow the syntax of MySQL.

waka
  • 3,362
  • 9
  • 35
  • 54
rahul1205
  • 794
  • 1
  • 6
  • 14