MySQL WHERE IN

Question

Is there a way (without JOIN) to use the WHERE clause on 2 columns (OR) IN a subquery? Currently, I'm doing

WHERE 'col1' IN
(
    SELECT id FROM table
) OR 'col2' IN
(
    SELECT id FROM table
)

And I'm sure I can do better :) . i've also tried WHERE ('col1', 'col2') IN <subquery> but MySQL says: Operand should contain 2 column(s)

Thanks for your help.

Edit: By "No join", I mean I'm alreeady making many joins: http://pastebin.com/bRfD21W9, and as you can see, the subqueries are on another table.

This is because I'm already joining a lot. I've updated my original post to add an extract of my current query. — Max13, Jul 09 '12 at 13:56
there are just 3 joins, i don't say there is a lot join.. i suggest you to make one more join instead of making inner query. — manurajhada, Jul 09 '12 at 14:13
Can you suggest me something? I don't see how to add it, as there is a notion of "OR" instead of "=" :/ — Max13, Jul 09 '12 at 14:17
i had eyes on your question, even i was also looking for another way if someone can suggest but.. not found yet. — manurajhada, Jul 09 '12 at 14:29
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13617/discussion-between-max13-and-manurajhada) — Max13, Jul 09 '12 at 14:41

score 35 · Answer 1 · answered Apr 07 '14 at 07:24

35

SELECT *
FROM table

WHERE 
(col_1, col_2) NOT IN
 (SELECT col_1, col_2 FROM table)

answered Apr 07 '14 at 07:24

Avinash Saini

1,203
11
10

score 6 · Answer 2 · answered Jul 09 '12 at 10:53

6

Rewrite your code like this and you're good to go:

WHERE ('col1', 'col2') IN
(
    SELECT id, id FROM table
)

answered Jul 09 '12 at 10:53

Matin Kh

5,192
6
53
77

4

It's closer than what I need. But `('col1', 'col2')` means "AND", and what I need is "OR" ^^' – Max13 Jul 09 '12 at 14:08

manurajhada · Accepted Answer · 2012-07-09T14:47:01.370

I Read you are not agree with JOIN, but just another way to do it.. See join with friends if it is useful to you..

SELECT `timeline`.`action`, `timeline`.`data`, `timeline`.`tlupdate`,
            u1.`id` AS ufrom_id, u1.`username` AS ufrom_username, u1.`firstname` AS ufrom_firstname, u1.`lastname` AS ufrom_lastname, u1.`picture` AS ufrom_picture,
            u2.`id` AS uto_id, u2.`username` AS uto_username, u2.`firstname` AS uto_firstname, u2.`lastname` AS uto_lastname, u2.`picture` AS uto_picture,
            m.`id` AS m_id, m.`name` AS m_name, m.`alternative_name` AS m_altname, m.`tiny_img` AS m_tiny, m.`normal_img` AS m_normal
    FROM `timeline`
    JOIN `users` u1 ON u1.`id` = `timeline`.`user_id_from`
    JOIN `users` u2 ON u2.`id` = `timeline`.`user_id_to`
    JOIN `friends` f on f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`
    JOIN `movies` m ON m.`id` = `timeline`.`movie_id`;

Update:

As you are using inner join You can this too to avoid the condition on complete resultSet.

JOIN `friends` f on ((f.`idol_id`=u1.`id` or f.`idol_id`=u2.`id`) and f.idol_id = ?)

Either you can use DISTINCT or use GROUP BY to get unique result.

Closer to what I need. Actually, I'm not so close minded (^^), I wanted to know if it was possible without JOIN. I'm not an SQL master, so thank you for your help. I've added your JOIN and a `WHERE idol_id = ?` to filter them, but I have many times the same rows (12 times, exact same answer) :/ Do you know how to avoid DUPLICATE? (I know the keyword `DISTINCT`, but I think it would be a hack if I use it) — Max13, Jul 09 '12 at 14:30
The data I want is `f.idol_id WHERE f.fan_id = ?`, so I think rewriting `f.idol_id = ?` to `f.fan_id = ?` will be enough ? — Max13, Jul 09 '12 at 14:57

score 2 · Answer 4 · answered May 07 '13 at 07:17

manurajhada answer is good. If you still decide to avoid the JOIN you could:

SELECT ... FROM (SELECT id FROM table) subquery, other_table
WHERE other_table.col1 = subquery.id OR other_table.col2 = subquery.id

This should make MySQL use the join buffer to hold the results of the subquery.

score 1 · Answer 5 · answered Sep 07 '21 at 14:12

1

select * from table1  WHERE (col1, col2) in (select subquerycol1, subquerycol2 from subquery_table where some_condition=1)

answered Sep 07 '21 at 14:12

Sarfraz Shaikh

492
1
3
11

score 0 · Answer 6 · answered Jul 09 '12 at 10:58

0

You can try below query:

select name from user_details where groupid IN ( select g_id from user_groups ) OR  cityid IN ( select c_id from user_cities );

Hope this help.

answered Jul 09 '12 at 10:58

metalfight - user868766

2,722
1
16
20

It could help, but the subqueries are the same. And I need something simplier than your query, which is my current query. – Max13 Jul 09 '12 at 13:59

score 0 · Answer 7 · edited Mar 09 '17 at 13:03

0

I also face similar problem

SELECT COUNT(*) FROM msg_old WHERE idfrom IN 
    (SELECT olduserid FROM temp_user)
    OR
    idto IN (SELECT olduserid FROM temp_user)

I came up with solution

SELECT * FROM msg_old 
INNER JOIN temp_user 
ON msg_old.IDFROM = temp_user.olduserid
OR msg_old.IDTO = temp_user.olduserid

edited Mar 09 '17 at 13:03

scytale

12,346
3
32
46

answered Nov 20 '14 at 08:34

sameer.nuna

454
1
5
6

The use of `OR` may potentially slowdown the query. See https://stackoverflow.com/questions/11702294/mysql-inner-join-with-or-condition. – Kenston Choi Jun 02 '22 at 10:13

sunlight · Answer 8 · 2012-07-09T10:50:00.997

-1

yes, you can do. It is easy enough. you must select same number of columns in sub query as you mentioned in the where clause.

edited Jul 09 '12 at 10:50

answered Jul 09 '12 at 10:44

sunlight

158
1
8

any example would help better !! – manurajhada Jul 09 '12 at 10:47
I think, he means @martin-kh query (below) – Max13 Jul 09 '12 at 13:58

MySQL WHERE IN

8 Answers8

Update: