Explain Codes LogoExplain Codes Logo

Select * from subquery

sql
subquery-aliasing
sql-queries
database-platforms
Nikita BarsukovbyNikita Barsukov·Sep 26, 2024
TLDR
SELECT * FROM (SELECT column1, column2 FROM your_table WHERE condition) AS 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:

-- Pro tip: Use aliases to avoid repeating yourself! SELECT sub.*, SUM(sub.column2) AS total_sum FROM ( SELECT column1, column2 FROM your_table WHERE condition ) AS sub GROUP BY sub.column1;

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:

-- This query is messier than a toddler's bedroom SELECT (SELECT SUM(column2) FROM your_table WHERE condition), column1, column2, column3, ... -- With aliases, same query is now cleaner than a monk's laundry! SELECT neat_sub.* FROM ( SELECT column1, column2, column3, ..., SUM(column2) AS total FROM your_table WHERE condition ) AS neat_sub;

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:

-- Oracle: Making things simple since 1979 SELECT sub.* FROM (SELECT column1, column2 FROM your_table) sub; -- Postgres: Similar to Oracle, but with a fancier 'AS' SELECT sub.* FROM (SELECT column1, column2 FROM your_table) AS sub;

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.