1

I'm editing some views and came across something that is new to me:

SELECT rn3.create_date
FROM receipt_note rn3
WHERE rn3.receipt_num = receipt_data.receipt_num

I'm just wondering what the rn3 does in the from part of the statement?

As there isn't a comma between them showing its another table and I dont see a table or view in my database called rn3.

carexcer
  • 1,407
  • 2
  • 15
  • 27
user3178424
  • 37
  • 1
  • 7
  • Sometimes you'll see it like `FROM receipt_note AS rn3` with the explicit "AS". These are equivalent, though the "AS" makes it a little more explicit that the "rn3" represents an alias. – Chris Farmer Jan 20 '14 at 16:00

1 Answers1

4

It is called an Alias.

You can define another name to use in your queries. Mostly used as shorter name of tables to simplify your queries. Example:

select t.some_column
from very_long_table_name t

Or if you join the same table twice then you need aliases to distinguish between the two. Example:

select child.name, parent.name
from users child
join users parent on child.parent_id = parent.id

And as stated in comments: When using DB engines other than Oracle, you can but don't need to define the as keyword:

select t.*
from some_table_name as t
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • The other benefit of using aliases (have seen this in some Oracle recommendations) that it saves memory needed to store SQL statements. Sounds weird in modern times with GBs of RAM but that is true. – Yaroslav Shabalin Jan 20 '14 at 18:58
  • I doubt that was a relyable source. Can you link to that? – juergen d Jan 20 '14 at 19:06
  • 2
    In Oracle you can't use `AS` for a table alias. –  Jan 20 '14 at 19:15
  • Although I personally find using `AS` preferable when possible as it makes things more readable (and would probably have saved the OP asking this question) – Basic Jan 20 '14 at 19:21
  • 1
    @juergend OK, I guess it was `Oracle Database 11g: SQL Fundamentals I`. I cannot give you a link as it is not public material. Exact quote is `Table aliases help to keep SQL code smaller, therefore using less memory.`. As I stated it sounds weird to me but it is official Oracle course. Also there is [a question on SO](http://stackoverflow.com/questions/8363108/how-does-table-alias-names-affect-performance) with conclusion that there is no or almost no real performance impact. I would agree with that. – Yaroslav Shabalin Jan 20 '14 at 19:24