How to use ANY command in SQL

Question

I have a following query description. How to express it in SQL?

Query -> list all the employee first name such that their emp_pct is less than any of the employee whose proj_num is 18 (Make use of ANY).

I have following table name EMP_2;

Name                           Type
-----------------------------------
EMP_NUM                        CHAR(3)
EMP_LNAME                      CHAR(15)
EMP_FNAME                      CHAR(15)
EMP_INITIAL                    CHAR(1)
EMP_HIREDATE                   DATE
JOB_CODE                       CHAR(3)
EMP_PCT                        NUMBER(5,2)
PROJ_NUM                       CHAR(3)

Laszlo Lugosi · Accepted Answer · 2016-09-13 09:40:38Z

3

Here you go. The ANY keyword compares and short circuits the moment it finds a matching row in the sub-query.

SELECT a.EMP_FNAME 
FROM EMP_2 a
WHERE a.EMP_PCT < ANY (
  select EMP_PCT 
  from EMP_2 
  where EMP_NUM <> a.EMP_NUM and PROJ_NUM = 18);

edited Sep 13, 2016 at 9:40

Laszlo Lugosi

3,8491 gold badge23 silver badges17 bronze badges

answered Sep 11, 2016 at 14:17

Bob Vale

18.5k1 gold badge44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Basil Bourque Over a year ago

See this Question for more discussion of ANY. And see MySQL doc.

Collectives™ on Stack Overflow

How to use ANY command in SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related