Feature: Add Query.as_table(name) for Inline View Support

[dhruvjain1512]

🚀 Feature Request: Add Query.as_table(name) for Inline View (Derived Table) Support

Problem Statement

Currently, creating derived tables in SQL queries requires verbose manual wrapping using subqueries and string formatting.
This approach is not only error-prone but also makes query composition difficult, especially when constructing complex analytical or layered SQL.

For example, users must manually render a query as a subquery, wrap it in parentheses, and then alias it before reuse:

sub = Query.from_(table).select(table.id, table.name)
main = Query.from_(
    f"({sub.get_sql()}) AS derived"
).select("*")

This process:

• Leads to redundant and inconsistent wrapping logic across projects.
• Increases the risk of syntax errors or incorrect alias handling.
• Breaks the fluent query-building chain that makes the library elegant and composable.

---

Proposed Solution

Introduce a method Query.as_table(name) that returns a Table object representing the subquery rendered as an inline view (derived table).
This enhancement will allow developers to use subqueries anywhere a table is expected—joins, selects, WHERE clauses, DELETE conditions, or even nested derived tables.

Expected Behavior

• The subquery must render exactly as:

  (<subquery_sql>) <alias>
  

  (with parentheses and a single space before the alias)
• Column lists and selected expressions should render in a stable order.
• Select lists must have no space after commas (e.g. SELECT id,name).
• Schema-qualified names (e.g., warehouse.products) must be preserved within subqueries.
• The resulting Table instance should correctly expose qualified identifiers such as alias.column (e.g., emp.id).
• The formatting and quoting behavior should remain adjustable through the existing get_sql() options (e.g., MySQLQuery.get_sql(quote_char="")).

---

Example Usage

# Build a subquery
sub = Query.from_(users).select(users.id, users.name).where(users.is_active == True)

# Convert subquery to a derived table
derived_users = sub.as_table("active_users")

# Use it seamlessly in another query
query = (
    Query.from_(derived_users)
    .join(departments).on(derived_users.id == departments.user_id)
    .select(derived_users.name, departments.dept_name)
)

print(query.get_sql())

Expected SQL output:

SELECT active_users.name,departments.dept_name
FROM (SELECT id,name FROM users WHERE is_active=TRUE) active_users
JOIN departments ON active_users.id=departments.user_id

---

Supported Scenarios

• Joins and subqueries in SELECT, WHERE, and DELETE contexts
• Nested derived tables
• Aggregates, GROUP BY, ORDER BY, HAVING clauses inside subqueries
• UNION and DISTINCT queries
• Proper handling of dialect-specific quoting (PostgreSQL "double quotes", MySQL backticks `)
• Stability in high-volume duplicate or nested alias scenarios

---

Benefits

✅ Enables clean, declarative creation of derived tables.
✅ Eliminates manual string manipulation and reduces human error.
✅ Preserves fluent query chaining for readable and composable SQL generation.
✅ Ensures dialect-specific correctness while maintaining flexibility.
✅ Makes the library more competitive with other query builders that support inline views natively.

---

Discussion Points

• Should as_table() automatically inherit schema and dialect settings from the parent query?
• Should alias validation (e.g., no reserved keywords) occur at render time or construction time?
• How should nested derived tables be handled for dialects that impose subquery limits?
• Should we allow additional parameters for alias quoting or schema scoping?

---

References & Inspiration

• SQLAlchemy’s subquery() and alias() methods for composable SQL.
• MySQL and PostgreSQL inline view syntax for derived tables.
• Query composability patterns in modern ORMs and analytical query builders.

---

Summary

Adding Query.as_table(name) would greatly improve the expressiveness and maintainability of SQL query composition.
It removes unnecessary boilerplate, reduces syntax risks, and makes complex multi-layered queries significantly easier to build and reason about — while preserving full control over SQL rendering and dialect-specific formatting.