CODEWITHSUNDEEP
mysqlsql
Darth Shekhar
Darth Shekhar

Reputation: 125

MySql where..in multiple columns

I need to implement the following query in MySql

SELECT name, value FROM opponent 
WHERE name in (SELECT name FROM noc WHERE fictive_status='fictional') 
AND value in (SELECT name FROM noc WHERE fictive_status='fictional')

I need to find all the rows in table opponent (name, value(some other name)) whose fictive_status (for both) in another table noc is 'fictional'.

Tried with 

SELECT name, value from opponent WHERE(name,value) in (SELECT name FROM noc WHERE fictive_status='fictional')

which requires 2 columns for operand and

Select name, value from opponent where (name and value) in (select name from noc where fictive_status='fictional')

which returns me all the rows in the opponent table.

How can implement in a better way?

Upvotes: 1

Views: 4187

Answers (3)

Prabhath Amaradasa
Prabhath Amaradasa

Reputation: 503

You can use following with EXISTS 

 SELECT o.name, o.value 
    FROM opponent AS o 
    WHERE EXISTS
    (
      SELECT * FROM noc As n1 WHERE n1.name = o.name  AND n1.fictive_status='fictional'
    )
    AND EXISTS
    (
       SELECT * FROM noc As n1 WHERE n1.name = o.value   AND n1.fictive_status='fictional'
    )

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562651

Using two subqueries like you do is inefficient because MySQL has to generate a large temporary table for each subquery, storing ever name from noc. It's a large temp table.

Instead, I suggest you use JOIN:

SELECT DISTINCT o.name, o.value 
FROM opponent AS o 
JOIN noc AS n1 ON n1.fictive_status='fictional' AND n1.name = o.name
JOIN noc AS n2 ON n2.fictive_status='fictional' AND n2.name = o.value

If you have the right index on noc(fictive_status, name) then this will not be expensive. It won't scan the whole table, it will only search for matching rows.

The DISTINCT is to reduce the result set in the case where you have multiple matches in noc.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Drum roll please . . . You can implement this in MySQL as:

SELECT o.name, o.value
FROM opponent o
WHERE o.name in (SELECT noc.name FROM noc WHERE noc.fictive_status = 'fictional') and
      o.value in (SELECT noc.name FROM noc WHERE noc.fictive_status = 'fictional');

This is actually your query with column aliases. There is no a priori reason to change it. The query uses ANSI standard SQL and should work in almost any database.

Upvotes: 1

Related Questions