StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Microsoft SQL Server's <a href="http://msdn.microsoft.com/en-us/library/ms188055.aspx" rel="noreferrer"><code>INTERSECT</code></a> <em>"returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand"</em> This is different from a standard <code>INNER JOIN</code> or <code>WHERE EXISTS</code> query.</p> <p><strong>SQL Server</strong></p> <pre><code>CREATE TABLE table_a ( id INT PRIMARY KEY, value VARCHAR(255) ); CREATE TABLE table_b ( id INT PRIMARY KEY, value VARCHAR(255) ); INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B'); INSERT INTO table_b VALUES (1, 'B'); SELECT value FROM table_a INTERSECT SELECT value FROM table_b value ----- B (1 rows affected) </code></pre> <p><strong>MySQL</strong></p> <pre><code>CREATE TABLE `table_a` ( `id` INT NOT NULL AUTO_INCREMENT, `value` varchar(255), PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `table_b` LIKE `table_a`; INSERT INTO table_a VALUES (1, 'A'), (2, 'B'), (3, 'B'); INSERT INTO table_b VALUES (1, 'B'); SELECT value FROM table_a INNER JOIN table_b USING (value); +-------+ | value | +-------+ | B | | B | +-------+ 2 rows in set (0.00 sec) SELECT value FROM table_a WHERE (value) IN (SELECT value FROM table_b); +-------+ | value | +-------+ | B | | B | +-------+ </code></pre> <p>With this particular question, the id column is involved, so duplicate values will not be returned, but for the sake of completeness, here's a MySQL alternative using <code>INNER JOIN</code> and <code>DISTINCT</code>:</p> <pre><code>SELECT DISTINCT value FROM table_a INNER JOIN table_b USING (value); +-------+ | value | +-------+ | B | +-------+ </code></pre> <p>And another example using <code>WHERE ... IN</code> and <code>DISTINCT</code>:</p> <pre><code>SELECT DISTINCT value FROM table_a WHERE (value) IN (SELECT value FROM table_b); +-------+ | value | +-------+ | B | +-------+ </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