StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It's not at all clear where "<code>value1</code>" is coming from, or "<code>uid2</code>" is coming from, or where the column "<code>like_id</code>" is coming from. Those column names do not appear in your sample table. Your example query references two different table names (<code>table</code> and <code>likes</code>), yet you only show data for one example table, and that table does not have a column named <code>like_id</code>.</p> <p>If we assume that "<code>value1</code>" and "<code>uid2</code>" in your query are literals, or bind parameters supplied to the query, which seems to be reasonable, given your specification (variously), of values of 1,2,3 and 4. But we're still left with "<code>like_id</code>" column. Given that it's referenced in the SELECT list of the IN subquery, we're going to presume that's a column in the "<code>likes</code>" table, and given that it's referenced in the outer query, we're going to assume that it's a column in the (unfortunately named) <code>table</code> table.</p> <p>(Bottomline, it's not at all clear how your query is returning a "correct" result, given that you've made it impossible to replicate a working test case.)</p> <hr> <p>Given a single table, as shown in your example data, e.g.</p> <pre><code>CREATE TABLE likes (id INT, name VARCHAR(4), uid INT); INSERT INTO likes VALUES (1,'bil',3),(2,'test',3),(3,'test',4) ,(4,'test',4),(5,'bil',5),(6,'bil',5); ALTER TABLE likes ADD PRIMARY KEY (id); ALTER TABLE likes ADD CONSTRAINT likes_ix UNIQUE KEY (uid, name); </code></pre> <p>Assuming that we're running a query against that single table, and that we're matching "likes" associated with uid=3 to "likes" associated with uid=4, and that the matching is done on the "name" column, then</p> <pre><code>SELECT t.id FROM `likes` t WHERE t.uid = 3 AND EXISTS ( SELECT 1 FROM `likes` s WHERE s.name = t.name AND s.uid = 4 ) </code></pre> <p>That will return the <code>id</code> of the row from the <code>likes</code> table for uid=3 where we also find a row in the <code>likes</code> table for uid=4 with a matching <code>name</code> value.</p> <p>Given a limited number of rows to be inspected from the <code>likes</code> table on the outer query, that gives a limited number of times a correlated subquery would need to be run, which should give reasonable performance:</p> <p>For large sets, a join operation generally performs better to return an equivalent result:</p> <pre><code>SELECT t.id FROM `likes` t JOIN `likes` s ON s.name = t.name AND s.uid = 4 WHERE t.uid = 3 GROUP BY t.id </code></pre> <p>The key to optimum performance for either query is going to be appropriate indexes.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload