CODEWITHSUNDEEP
mysqloptimization
Mikhail
Mikhail

Reputation: 9007

Peculiarities of MySQL's IN clause

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

In my design I needed to filter out rows that either did not require a certain skill, or for which the skill name and the skill level were met. Here's the solution:

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

I didn't know that expr IN (value,...), as according to dev.mysql.com both expr and value can be multiple columns.

What is this called, and are there more shortcuts like these?
More importantly - is there a way to convert this query to match skilllevel as "greater than or equal to"?

Solution as per @yokoloko (though he won't admit it)

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

Upvotes: 4

Views: 134

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562300

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

Here's how I'd 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'

As for terminology of your query, @guide points out that you ran a row subquery, and compared it to a literal row using row constructor syntax. SQL allows you to create anonymous rows using columns, expressions, or values, and use them in certain circumstances, like comparing to another row with a compatible number of columns. I'd call this row comparison.

You can also use this in a join condition, but if you want equality comparison for one column and inequality for another column, it gets really unclear:

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

The above would probably not do what you want. Example:

SELECT (1,2) = (1,2);   -- returns true (i.e. 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 is applied to all columns in these rows. But it also short-circuits (unlike AND):

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

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

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

Smith is greater than Anderson, but Agent is not greater than Thomas. Nevertheless, we know how we would order these two names in a phone book.

The point of all this is that you need to write complete boolean expressions if you want more control over the comparison.

Upvotes: 3

guido
guido

Reputation: 19194

That's called row subquery

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

Upvotes: 2

yokoloko
yokoloko

Reputation: 2860

i don't know how is it called but if I understand well you can do something like this to get the greater than : 

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 don't know if it's really well optimized.

Upvotes: 0

Related Questions