CODEWITHSUNDEEP
sqloracle-database
DixieFlatline
DixieFlatline

Reputation: 484

Querying Table vs Querying Subquery of Same Table

I'm working with some very old legacy code, and I've seen a few queries that are structured like this

SELECT
    FieldA,
    FieldB,
    FieldC
FROM
    (
     SELECT * FROM TABLE1
    )
    LEFT JOIN TABLE2 ON...

Is there any advantage to writing a query this way?

This is in Oracle.

Upvotes: 0

Views: 361

Answers (2)

Little Santi
Little Santi

Reputation: 8813

As a basic good practice in SQL, you should not code a full-scan from a table (SELECT * FROM table, without a WHERE clause), unless necessary, for performance issues.

In this case, it's not necessary: The same result can be obtained by:

SELECT
    Fields
FROM
    TABLE1 LEFT JOIN TABLE2 ON...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

There would seem to be no advantage to using a subquery like this. The reason may be a historical relic, regarding the code.

Perhaps once upon a time, there was a more complicated query there. The query was replaced by a table/view, and the author simply left the original structure.

Similarly, once upon a time, perhaps a column needed to be calculated (say for the outer query or select). This column was then included in the table/view, but the structure remained.

I'm pretty sure that Oracle is smart enough to ignore the subquery when optimizing the query. Not all databases are that smart, but you might want to clean-up the code. At the very least, such as subquery looks awkward.

Upvotes: 4

Related Questions