I am working on some select query and I found this syntax

               FROM EMPLOI
              WHERE MLE = A.MLE)

I have never seen this before.

is it possible to test if an integer is in a list of values?

Normally, the clause in must verify if a column value corresponds to a list of the values of the subquery.

  • Would you consider WHERE 7 IN (5,6,7,8) to be invalid, too? ;) – ypercubeᵀᴹ Mar 8 '18 at 22:26
  • I don t know. I am not understanding this syntax. The query is working fine but I not understanding how it works – Adel Essafi Mar 8 '18 at 22:28

IN can be followed by a list of values (one or more), or a subquery returning any number of rows consisting of 1 column. These syntaxes are logically equivalent.

For example:

x IN (SELECT id FROM user)

is equivalent to providing explicitly the list of id's:

x IN (1, 2, 3, 4, 5)

In both cases, IN can have any expression as lefthand operand. For example:

  • A column name: col IN (SELECT ...)
  • A number: 7 IN (SELECT ...)
  • A text: 'hello world' IN (SELECT ...)
  • An arithmetic espression: 1 - 5 IN (SELECT ...)
  • A function: PI() IN (SELECT ...)

In all these cases the DBMS will evaluate the expression on the left, and then the subquery. If at least one value returned by the subquery is equal to the left operand, the whole IN expression returns TRUE.

  • is there any good example explaining this on the net? I googled but get not a good illustrative one – Adel Essafi Mar 8 '18 at 22:34
  • @AdelEssafi - Well, there's this question and answer, now. Also, check the syntax for WHERE and search conditions for your DBMS (for example, for SQL Server, WHERE and search conditions). – RDFozz Mar 8 '18 at 22:44
  • Since @AdelEssafi still needed examples or clarification, I extended my answer. I hope that now it is clear/exhaustive enough. Let me know if you have questions. – Federico Razzoli Mar 8 '18 at 22:54
  • 1
    @AdelEssafi Note that (in most DBMS) you can also compare tuples with the IN clause , e.g. where (a,b) in (select x,y from ...) or where (1,2) in (select x,y from ..) or where (a,b) in (values (1,2), (2,3), (3,4)) – a_horse_with_no_name Mar 9 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.