How to Properly Alias Tables in Oracle SQL Queries?

Correct use of table aliases in Oracle SQL queries

When aliasing a table using the AS keyword in Oracle, you may encounter the error "ORA-00933: SQL command not properly ended". This error stems from Oracle not supporting table aliasing using the AS keyword.

In Oracle, the syntax for table aliases is a comma-separated list of table names, followed by a colon (:), and then the desired alias. For example:

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest, Stay S
WHERE G.Guest_ID = S.Guest_ID AND G.City = 'Miami' AND S.Room = '222';

Or the more recommended modern SQL standard JOIN syntax:

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest G
JOIN Stay S ON G.Guest_ID = S.Guest_ID
WHERE G.City = 'Miami' AND S.Room = '222';

To resolve the error in the original query, simply remove the AS keyword. The second example above shows a cleaner, more maintainable JOIN syntax that avoids the ambiguity of implicit joins and makes the join conditions explicit. Modern SQL development strongly recommends using JOIN syntax. While Oracle allows the old-style comma-separated join syntax, it is error-prone and difficult to read.

This modified query will execute successfully without table aliasing using the AS keyword.

The above is the detailed content of How to Properly Alias Tables in Oracle SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!