Subquery in FROM must have an alias

Why aliases in subqueries?

Every subquery nested in the FROM clause must be given an alias. This allows the outer query to use it like a normal table. The alias serves as an identifier enhancing the clarity and manageability of the query.

Efficient use of aliases

Direct Subquery Aliasing

For a single or multiple columns in a subquery, assigning an alias is a walk in the park:

-- Lone Ranger (single column)
SELECT main_column
FROM (SELECT sub_column FROM sub_table WHERE condition) AS sub_alias;

-- Avengers Assemble (multiple columns)
SELECT main_column
FROM (SELECT sub_column1, sub_column2 FROM sub_table WHERE condition) AS sub_alias;


Aliases for Aggregated Results

When aggregating data in a subquery, using an alias lets the outer query access the computed column without breaking a sweat:

SELECT main_query_column, sub_alias.total_count
FROM (
  SELECT COUNT(sum_column) AS total_count
  FROM sub_table
  GROUP BY grouping_column
) AS sub_alias;


Working with Joins

When subqueries join the party with JOINS, giving each one its own alias will avoid any identity crisis:

SELECT a.column1, b.column2
FROM table1 AS a
JOIN (
  SELECT column2, column3 FROM table2 WHERE condition
) AS b ON a.column1 = b.column3;


Set Operations

Alias is the secret password when you are operating a set operation likeEXCEPT in PostgreSQL (equivalent to MINUS in Oracle):

SELECT column FROM table1
EXCEPT
SELECT column FROM (
  SELECT column FROM table2 WHERE condition
) AS subset; -- "subset" sounds very neat, isn't it?


Parentheses are the bouncers determining the order of operations, especially when it's a compound set operation.

DISTINCT and WHERE clause

For counting distinct values or applying a filter, aliases serve as your north star:

-- Counting distinct values: It's like finding unique snowflakes!
SELECT COUNT(distinct user_alias.id)
FROM (SELECT id FROM users WHERE active = true) AS user_alias;

-- Applying a WHERE clause: Only VIPs (values) allowed here!
SELECT * FROM (SELECT id, name FROM users WHERE name LIKE 'A%') AS user_data
WHERE user_data.id > 100; -- "Sorry, IDs under 100, not tonight!"


Advanced Aliasing Tips

Correlated Subqueries

A corridor, or rather, a correlated subquery often needs an alias for reference between the subquery and the outer query:

SELECT a.*, user_data.total_posts
FROM authors AS a
JOIN (
  SELECT author_id, COUNT(*) AS total_posts
  FROM posts
  WHERE posts.author_id = a.id -- "Hey, ID, do you have a twin brother?"
  GROUP BY author_id
) AS user_data ON user_data.author_id = a.id;


Recursive Subqueries

Recursive CTE (Common Table Expressions) also demand aliases to refer to different iterations:

WITH RECURSIVE sub_alias AS (
  SELECT id, parent_id FROM table
  UNION ALL
  SELECT t.id, t.parent_id FROM table AS t, sub_alias AS s
  WHERE t.parent_id = s.id
) 
SELECT * FROM sub_alias; -- "I'm not a subquery, I'm a CTE alias!"


Stay sharp! SQL syntax and its handling of subqueries and their aliases could turn tables around between PostgreSQL, MySQL, Oracle, and SQL Server.