Oracle query getting missing key word exception

Question

SELECT *
FROM Orders
INNER JOIN (select * from customers) as t
ON Orders.CustomerID=t.CustomerID;

When same query try to execute in mysql it return correct response. but in the case of oracle its returning missing keyword exception.

Answer 1

1. Remove the AS keyword. In Oracle, you only need it for column alias and not for table alias.

2. Also, the in-line view is not required, you could simply use the table_name customers.

3. Avoid using * in production systems. Use required columns in the SELECT list with proper aliasing if you have similar columns.

    SELECT     o.column_list, 
               t.column_list
    FROM       Orders o
    INNER JOIN customers t
    ON         o.CustomerID = t.CustomerID;

Answer 2

Oracle SELECT grammar doesn't allow 'AS' keyword before inline view alias, so

SELECT * FROM Orders INNER JOIN (select * from customers) t ON Orders.CustomerID=t.CustomerID;

And instead of (select * from customers) t you can just use customers t.