How to Give an Alias to a Table in Oracle

How can I give an alias to a table in Oracle?

Oracle doesn't allow as for table aliases, only column aliases. So do:

select count(id)
from users usr
where usr.dept = 'SVC';

oracle: can you assign an alias to the from clause?

Yes, Oracle supports table aliases. It supports AS in the SELECT list but not in the FROM list:

SELECT a.col - b.col AS markup
  FROM RETAIL a,
       COST b
 WHERE b.id = a.id

Most databases support omitting the AS keyword.

That said, table aliases aren't column aliases -- you still need to reference a specific column in the respective table in the SELECT clause, like you see in my update of your example. I also added the WHERE criteria so the query wouldn't be returning a Cartesian product.

Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):

SELECT x.col
  FROM (SELECT t.col,
               MAX(t.date)
          FROM TABLE t
      GROUP BY t.col) x

Here's your query:

Your problem was you were putting the table alias inside the derived table, when it needs to be outside the brackets/parenthesis:

SELECT DISTINCT TO_CHAR(MONTHS_BETWEEN(x.pubdate, y.pubdate), '99.99') AS "Answer"
 FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 a WHERE a.pubid = 2) x,
      (SELECT DISTINCT b.pubdate FROM BOOKS3 b WHERE b.pubid = 4) y

The reason you need the distinct is because of the Cartesian product.

How to use the 'as' keyword to alias a table in Oracle?

You can use AS for table aliasing on many SQL servers (at least MsSQL, MySQL, PostrgreSQL) but it's always optional and on Oracle it's illegal.

So remove the AS :

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest G

How to use as to set alias for joined tables in oracle 10

You can't directly name the result of a join. One option is to use a subquery:

select T.id
from (
  select *
  from table1
  inner join table2 on table1.x = table2.y
  inner join table3 on table3.z = table1.w
) T

Another option is subquery factoring:

with T as (
  select *
  from table1
  inner join table2 on table1.x = table2.y
  inner join table3 on table3.z = table1.w
)
select T.id
from T

How to perform a join with aliases for two tables in oracle?

The fixed query:

SELECT A.ID,
       A.NAME,
       A.STORE,
       B.STOREADDRESS as yourAlias /* AS is ok for column aliases ... */
FROM TableALong A                  /* ... but not for table aliases */
     LEFT OUTER JOIN TableBLong  B /* JOIN syntax */
       ON (A.NAME = B.NAME and A.STORE = B.STORE)
WHERE ...

Instead of LEFT OUTER you could have INNER, FULL OUTER, ...; see here for more.

Alias For partition table SQL

Yo didn't call any columns from 2nd table (t2). You may follow below code

select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5,t2.col_1,t2.col_2
from tab1.tab1_1 partition(p20191231) t1 
LEFT JOIN ( 
            select t2.col_1 AS col_1, t2.col_2
            from (tab2.tab2_2  partition (p20191231)) t2
            where date between '1-Dec-2019' and '31-Dec-2019') t2
            ON t1.col1 = col_1
where 
        t1.col6 between '1-Dec-2019' AND '31-Dec-2019';

or even simplified by

select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5,t2.col_1,t2.col_2
from 
  tab1.tab1_1 partition(p20191231) t1, 
  tab2.tab2_2  partition(p20191231) t2
where 
  t2.date between '1-Dec-2019' and '31-Dec-2019' and
  t1.col1 = col_1 and 
  t1.col6 between '1-Dec-2019' AND '31-Dec-2019';

Is the 'as' keyword required in Oracle to define an alias?

According to the select_list Oracle select documentation the AS is optional.

As a personal note I think it is easier to read with the AS

---

Related Topics