SQL Server/T-SQL/Select Query/ANY
Any: If your subquery returns a scalar value, you can use a comparison operator
<source lang="sql">
1> 2> create table employee( 3> ID int, 4> name nvarchar (10), 5> salary int ) 6> GO 1> 2> create table job( 3> ID int, 4> title nvarchar (10), 5> averageSalary int) 6> GO 1> 2> 3> insert into employee (ID, name, salary) values (1, "Jason", 1234) 4> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (2, "Robert", 4321) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (3, "Celia", 5432) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (4, "Linda", 3456) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (5, "David", 7654) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (6, "James", 4567) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (7, "Alison", 8744) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (8, "Chris", 9875) 2> GO (1 rows affected) 1> insert into employee (ID, name, salary) values (9, "Mary", 2345) 2> GO (1 rows affected) 1> 2> insert into job(ID, title, averageSalary) values(1,"Developer",3000) 3> GO (1 rows affected) 1> insert into job(ID, title, averageSalary) values(2,"Tester", 4000) 2> GO (1 rows affected) 1> insert into job(ID, title, averageSalary) values(3,"Designer", 5000) 2> GO (1 rows affected) 1> insert into job(ID, title, averageSalary) values(4,"Programmer", 6000) 2> GO (1 rows affected) 1> 2> 3> select * from employee; 4> GO ID name salary
---------- -----------
1 Jason 1234 2 Robert 4321 3 Celia 5432 4 Linda 3456 5 David 7654 6 James 4567 7 Alison 8744 8 Chris 9875 9 Mary 2345
(9 rows affected) 1> select * from job; 2> GO ID title averageSalary
---------- -------------
1 Developer 3000 2 Tester 4000 3 Designer 5000 4 Programmer 6000
(4 rows affected) 1> 2> 3> -- If your subquery returns a scalar value, you can use a comparison operator, 4> 5> SELECT e.ID,e.name 6> FROM Employee e 7> WHERE e.salary > ANY (SELECT averageSalary FROM job j) 8> GO ID name
----------
2 Robert 3 Celia 4 Linda 5 David 6 James 7 Alison 8 Chris
(7 rows affected) 1> 2> 3> drop table employee; 4> drop table job; 5> GO 1> 2>
</source>