StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Avoiding subselects used for IN clauses:-</p> <pre><code>SELECT * FROM users_table INNER JOIN ( SELECT Sub1.user_id FROM ( SELECT COUNT(*) AS counter, user_id FROM ( SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119) UNION ALL SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226) UNION ALL SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525) UNION ALL SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128) ) thegroups GROUP BY user_id HAVING counter = 4 ) Sub1 LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2 ON group_user_map.user_id = Sub2.user_id WHERE Sub2.user_id IS NULL ) Sub3 ON users_table.username = Sub3.user_id </code></pre> <p>Or avoiding using the COUNTs to check that the user id exists in all 4 tables, instead using inner joins</p> <pre><code>SELECT * FROM users_table INNER JOIN ( SELECT Sub1.user_id FROM ( SELECT z.user_id FROM ( SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)) z INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)) y ON z.user_id = y.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)) x ON z.user_id = x.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)) w ON z.user_id = w.user_id ) Sub1 LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2 ON group_user_map.user_id = Sub2.user_id WHERE Sub2.user_id IS NULL ) Sub3 ON users_table.username = Sub3.user_id </code></pre> <p>Cleaning up that 2nd query a bit</p> <pre><code>SELECT * FROM users_table INNER JOIN ( SELECT z.user_id FROM (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)) z INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)) y ON z.user_id = y.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)) x ON z.user_id = x.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)) w ON z.user_id = w.user_id LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2 ON z.user_id = Sub2.user_id WHERE Sub2.user_id IS NULL ) Sub3 ON users_table.username = Sub3.user_id </code></pre> <p>Using your SQL in the comment below, it can be cleaned up to :-</p> <pre><code>select SQL_NO_CACHE id from users_table INNER JOIN ( SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (0, 67) ) ij1 ON users_table.username = ij1.user_id LEFT OUTER JOIN ( SELECT user_id FROM group_user_map WHERE group_id IN (0) ) Sub2 ON users_table.username = Sub2.user_id WHERE Sub2.user_id IS NULL </code></pre> <p>Cleaning up my SQL in the same way:-</p> <pre><code>SELECT users_table.* FROM users_table INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2601,119)) z ON users_table.username = z.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (58,226)) y ON users_table.username = y.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (1299,525)) x ON users_table.username = x.user_id INNER JOIN (SELECT distinct(user_id) FROM group_user_map WHERE group_id IN (2524,128)) w ON users_table.username = w.user_id LEFT OUTER JOIN (SELECT user_id FROM group_user_map WHERE group_id IN (2572)) Sub2 ON users_table.username = Sub2.user_id WHERE Sub2.user_id IS NULL </code></pre> <p>Removing the subselects and doing the joins directly (might help or hinder, suspect it will depend on how many duplicate user_id records there are for each set of group_id records)</p> <pre><code>SELECT DISTINCT users_table.* FROM users_table INNER JOIN group_user_map z ON users_table.username = z.user_id AND z.group_id IN (2601,119) INNER JOIN group_user_map y ON users_table.username = y.user_id AND y.group_id IN (58,226) INNER JOIN group_user_map x ON users_table.username = x.user_id AND x.group_id IN (1299,525) INNER JOIN group_user_map w ON users_table.username = w.user_id AND w.group_id IN (2524,128) LEFT OUTER JOIN group_user_map Sub2 ON users_table.username = Sub2.user_id AND Sub2.group_id IN (2572) WHERE Sub2.user_id IS NULL </code></pre>

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