Hey.

Question on sql on "Subqueries". There is a scalar subquery - it returns one value. A scalar subquery can be placed, for example, to the right of the = operator. That is, a scalar query returns a single row and shows one column in a row — in my hand is a cell from which you can pull a VALUE (for example, a number) and put this value to the right of the equal sign. There are no questions for scalar subquery yet. With scalar queries, the operators <,>, <= ... are applied.

The request can also return a TABLE or STRING. This is a non-scalable query. They write that to work with such non-scalable queries there are operators IN,ANY,ALL .. I don’t understand how, for example, a query that returns several lines to be pushed into the same IN is obtained ..IN (and here is the table) .

In IN you can pass multiple values ​​separated by commas (... IN(1,3,5); - this use case is clear to me). For example, this

 ...WHERE id IN(1,3,5);

You can rewrite it like this:

 ...WHERE id=1 OR id=3 OR id=5;
  • one
    But if your subquery returns 3 rows consisting of one column with values ​​1, 3 and 5, then it will be similar to IN (1,3,5) - Mike
  • Mike, as I understand it, the operators IN, ANY, ALL can only "throw" a column table. these operators sort this column into data cells and write them separated by commas. I understand correctly? - Dimon
  • one
    Almost correct. because you are not familiar with the multi-column IN. for example (x,y) IN( (1,2), (3,4) ) similarly (x=1 and y=2) OR (x=3 and y=4) for this variant the subquery should return a two-column table - Mike
  • The IN, ANY and unspecified SOME statements are completely equivalent (the difference is only in syntax) - it is enough for them that at least one record from the set that returned the subquery matches the record from the table (s) of the main query. But the ALL operator is not enough - it needs to match ALL the records returned by the subquery, i.e. he checks not that "there is an equal," but that "there is no non-equal." - Akina
  • Ps. (Almost offtopic based on a query can also return a TABLE or STRING. ) A query ALWAYS returns a set of records. Another thing is that this set can contain none, one or more than one record, and the structure of the record contains one field or more than one. The word "scalar" matches the query, in the return structure of which there is strictly 1 field, and 0 or 1 record is returned. Under the word "string" is suitable query that returns 0 or 1 record (including those that are scalar). Well, the word "table" covers any options. - Akina

0