Understanding the Difference Between Derived Tables and Scalar Subqueries in SQL

In MySQL

While practicing SQL queries involving subqueries, I encountered an error that looked confusing at first:

Error Code: 1248
Every derived table must have its own alias

The interesting part was this:

One query failed with the error. Another query using a subquery worked perfectly fine without any alias.

At first glance, both looked similar because both contained subqueries. But internally, SQL treats them very differently.

That’s when I learned one of the most important SQL concepts. The difference between:

• Derived tables
• Scalar subqueries

Understanding this distinction makes subqueries much easier to work with in real-world database systems and SQL interviews.

The First Query & Why It Failed

Consider this query:

SELECT COUNT(*)
FROM (
    SELECT e.emp_name, d.dept_name
    FROM Employee AS e
    INNER JOIN Dept AS d
    ON e.dept_id = d.dept_id
);

This produces the error:

Every derived table must have its own alias

What Actually Happened?

The important part is:

FROM (
    SELECT ...
)

A subquery inside the FROM clause creates a temporary table.

This temporary table is called a Derived table.

MySQL treats it exactly like a real table. And in SQL, every table must have a name. That name is called an alias.

The Correct Fix

We simply assign an alias to the derived table:

SELECT COUNT(*)
FROM (
    SELECT e.emp_name, d.dept_name
    FROM Employee e
    INNER JOIN Dept d
    ON e.dept_id = d.dept_id
) AS temp_table;

Now the query executes correctly.

Core idea

A subquery inside FROM behaves like a table. So MySQL internally asks:

“What is this table called?”

Without an alias, SQL cannot reference that temporary result set properly.

The Second Query & Why It Worked Without Alias

Now consider this query:

SELECT *
FROM Invoice
WHERE Total >= (
    SELECT AVG(Total)
    FROM Invoice
);

This query works perfectly. No alias needed. So why is this different?

Understanding Scalar Subqueries

This subquery:

(
    SELECT AVG(Total)
    FROM Invoice
)

returns a single value.

Example:

42.5

This is called a Scalar subquery

Unlike a derived table, this is not treated as a table. It is treated as a single value expression. That is why no alias is required.

The Big Difference

Subquery in FROM return Table, so alias name is needed.

Subquery in WHERE return single value, hence no alias name required.

Logic Behind It

Case 1: Derived Table

FROM (
   SELECT ...
)

This creates:

• Temporary rows
• Temporary columns
• A temporary table structure

Since it behaves like a table, it requires an alias.

Example:

SELECT *
FROM (
   SELECT emp_name
   FROM Employee
) AS temp;

Alias is mandatory.

Case 2: Scalar Subquery

WHERE Total >= (
   SELECT AVG(Total)
)

This returns a single value like:

50.75

No rows and columns structure. No table behavior. So, no alias is needed.

Another example:

SELECT *
FROM Employee
WHERE salary > (
   SELECT AVG(salary)
   FROM Employee
);

This works perfectly without aliases because the subquery produces only one value.

Guideline

If a subquery behaves like a table, alias is required.

If a subquery returns a value, alias is unnecessary.

Essential insights

My first query failed because:

• The subquery was inside FROMclause, so SQL treated it like a table. Tables require aliases.

My second query worked because:

• The subquery returned a single value. It was treated as an expression. No alias was necessary

Once I understood this difference, SQL subqueries became much easier to read, debug, and design.