salary > avg(salary) conundrum

Beginners, and I think even some non-beginners would want this to work:

select *
from employees 
where salary > AVG(salary)

The problem is that avg is applied to the filtered rows. It will be circular how avg(salary) will get its value if avg is part of where clause. So to prevent that confusion, SQL standard disallows using aggregate functions in where clause.

Humans are lazy. Programmers are humans. Programmers are lazy.

I think that it is a missed opportunity that SQL didn't impose aliases when tables are referenced, otherwise they could introduce functionality that would have no ambiguity:

select e.*
from employees e
where e.salary > AVG(employees.salary)

Or perhaps a little OOP:

select e.*
from employees e
where e.salary > employees.AVG(salary)

That would mean, get all the employee (denoted by e) from employees whose salary is greater than the average salary of all employees.

Before you scoff that it would be super-duper hard for the RDBMS developers to parse things like that. Consider that, that sort of brevity can be achieved in C#'s Linq:

Live test: https://dotnetfiddle.net/uXJEZF

using System;
using System.Linq;

class Employee 
{
    public string Name { get; set; }
    public decimal Salary { get; set; }
}

public class Simple 
{
    public static void Main () 
    {
        var employees = new Employee[]
        {
            new Employee { Name = "John", Salary = 10 },
            new Employee { Name = "Paul", Salary = 9 },
            new Employee { Name = "George", Salary = 2 },
            new Employee { Name = "Ringo", Salary = 1 },
        };

        Console.WriteLine ("Average salary: {0}", employees.Average (x => x.Salary));

        var query = 
            from e in employees
            where e.Salary > employees.Average(x => x.Salary)
            select e;

        foreach (var e in query)
        {
            Console.WriteLine ("{0} {1}", e.Name, e.Salary);
        }
    }
}

Output:

Average salary: 5.5
John 10
Paul 9

If RDBMS have OOP and Linq syntax, it can prevent unusual request:



Live test: https://dotnetfiddle.net/PiANMM