Select * from subquery

sql

subquery-aliasing

sql-queries

database-platforms

byNikita Barsukov·Sep 26, 2024

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.