Features of MySQL IN

Question

I just solved an interesting problem using MySQL IN, but I don't know what this "trick" is called.

In my project, I needed to filter out lines that either did not require a specific skill, or for which a skill name and skill level were specified. Here's the solution:

SELECT * FROM table WHERE skillname = "" OR (skillname, skilllevel) IN ( SELECT name, level FROM skills WHERE user="Mikhail" ) 

I did not know that expr IN ( value , ...) , according to dev.mysql.com, both expr and value can be multiple columns.

What is called, and are there labels like these?
More importantly, is there a way to convert this query to skilllevel as more or equal?

---

Solution according to @yokoloko (although he does not recognize this)

 SELECT * FROM t1 WHERE skillname = "" OR skillname IN ( SELECT name FROM skills WHERE user="Mikhail" AND level >= t1.skilllevel ) 

Answer 1

I don't know what it's called, but if I understand well, you can do something like this to get more:

 SELECT * FROM table t1 WHERE skillname = "" OR (skillname, skilllevel) IN ( SELECT name, level FROM skills s WHERE user="Mikhail" and t1.skilllevel >= s.level ) 

But I do not know if it is really optimized.

Answer 2

Sorry, the IN predicate always compares values ​​for equality, not greater than or equal to.

Here's how I write it:

 SELECT table.* FROM table WHERE skillname = '' UNION ALL SELECT table.* FROM table JOIN skills ON table.skillname = skills.name AND table.skilllevel >= skills.level WHERE skills.user = 'Mikhail' 

Regarding the terminology of your query, @guide indicates that you ran a subquery of strings and compared it to a literal string using the string constructor syntax. SQL allows you to create anonymous rows using columns, expressions or values ​​and use them in certain circumstances, for example, compared to another row with a compatible number of columns. I would call this string comparison.

You can also use this in a join condition, but if you want to compare equality for one column and inequality for another column, it becomes really obscure:

 SELECT table.* FROM table JOIN skills ON (table.skillname, table.skilllevel) >= (skills.name, skills.level) WHERE skills.user = 'Mikhail' 

The above probably won't do what you want. Example:

 SELECT (1,2) = (1,2); -- returns true (ie 1 in MySQL) SELECT (1,2) >= (1,2); -- returns true SELECT (1,3) >= (1,2); -- returns true SELECT (1,2) >= (1,3); -- returns false SELECT (2,3) >= (1,2); -- returns true SELECT (1,3) >= (2,2); -- returns false 

The same comparison operator applies to all columns in these rows. But this is also a short circuit (unlike AND ):

 SELECT (2,3) >= (1,4); -- returns true 

2 is greater than 1, but 3 is not greater than 4. Confusing? Consider an example of the alphabet:

 SELECT ('Smith', 'Agent') >= ('Anderson', 'Thomas'); -- returns true 

Smith is bigger than Anderson, but Agent is no bigger than Thomas. However, we know how we will order these two names in the phone book.

The point is that you need to write complete Boolean expressions if you want more control over the comparison.

Answer 3

This is called a row subquery.

A row subquery is a variant of a subquery that returns one row and can thus return more than one column value.