Select * from subquery
Focus on selecting specific columns and applying a where condition inside your subquery. Assign an alias to it (we've used subquery
here) to effectively treat it as a virtual table. Use SELECT *
from the aliased subquery to efficiently recover all results.
Write smart with aliases
Aliases prove their worth in SQL by gifting your code greater efficiency and readability. They shine especially when you have calculations like SUM() in your subquery:
Here, sub.*
fetches all columns from our subquery, while allowing us to calculate the total sum of column2
.
Structure your query for better maintainability
SQL queries are like a sourdough starter - without proper care, they can get out of hand. Here's a before-and-after demonstration on how subquery aliases promote code maintainability:
Oracle and Postgres: How to use subquery aliases efficiently
Subquery aliasing is a fundamental concept. However, its syntax slightly differs depending on the database platform. Let's take a look at Oracle and Postgres:
On both platforms, sub
is used to effortlessly select all columns within the subquery.
Troubles with subqueries: Unfit puzzle pieces
Sometimes, your subquery puzzle pieces may not fit. Here's what to check when that happens:
- Syntax issues: Verify that your database's specific requirements for
AS
keyword in aliases is adhered to. - Alignment problems: Make sure your subquery's selected columns align with corresponding aliases in the outer query.
- Aggregation mishaps: When using aggregate functions like
SUM()
, don't forget to include proper GROUP BY clause.
Was this article helpful?