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
No comments:
Post a Comment