Alternative to Intersect in MySQL

Question

I need to implement the following query in MySQL    select   from emovis reporting where  id 3 and cut name          and cut name          intersect   select   from emovis reporting where  id 3  and   cut name         or cut name           I know that intersect is not in MySQL  So I need another way  Please guide me

User · Answer

There is a more effective way of generating an intersect, by using UNION ALL and GROUP BY. Performances are twice better according to my tests on large datasets.

Example:

SELECT t1.value from (
  (SELECT DISTINCT value FROM table_a)
  UNION ALL 
  (SELECT DISTINCT value FROM table_b)
) AS t1 GROUP BY value HAVING count(*) >= 2;

It is more effective, because with the INNER JOIN solution, MySQL will look up for the results of the first query, then for each row, look up for the result in the second query. With the UNION ALL-GROUP BY solution, it will query results of the first query, results of the second query, then group the results all together at once.

User · Answer

AFAIR  MySQL implements INTERSECT through INNER JOIN

User · Answer

For completeness here is another method for emulating INTERSECT  Note that the IN  SELECT      form suggested in other answers is generally more efficient   Generally for a table called mytable with a primary key called id   SELECT id FROM mytable AS a INNER JOIN mytable AS b ON a id   b id WHERE  a col1    someval   AND  b col1    someotherval      Note that if you use SELECT   with this query you will get twice as many columns as are defined in mytable  this is because INNER JOIN generates a Cartesian product   The INNER JOIN here generates every permutation of row-pairs from your table  That means every combination of rows is generated  in every possible order  The WHERE clause then filters the a side of the pair  then the b side  The result is that only rows which satisfy both conditions are returned  just like intersection two queries would do

User · Answer

Microsoft SQL Server s INTERSECT  returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand   This is different from a standard INNER JOIN or WHERE EXISTS query   SQL Server  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    MySQL  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        -------    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 INNER JOIN and DISTINCT   SELECT DISTINCT value FROM table a INNER JOIN table b USING  value     -------    value    -------    B        -------    And another example using WHERE     IN and DISTINCT   SELECT DISTINCT value FROM table a WHERE  value  IN  SELECT value FROM table b     -------    value    -------    B        -------

User · Answer

Your query would always return an empty recordset since cut name          and cut name      will never evaluate to true   In general  INTERSECT in MySQL should be emulated like this   SELECT    FROM    mytable m WHERE   EXISTS                   SELECT  NULL         FROM    othertable o         WHERE    o col1   m col1 OR  m col1 IS NULL AND o col1 IS NULL                   AND  o col2   m col2 OR  m col2 IS NULL AND o col2 IS NULL                   AND  o col3   m col3 OR  m col3 IS NULL AND o col3 IS NULL               If both your tables have columns marked as NOT NULL  you can omit the IS NULL parts and rewrite the query with a slightly more efficient IN   SELECT    FROM    mytable m WHERE    col1  col2  col3  IN                   SELECT  col1  col2  col3         FROM    othertable o

User · Answer

Break your problem in 2 statements  firstly  you want to select all if   id 3 and cut name          and cut name         is true   Secondly  you want to select all if    id 3  and   cut name         or cut name         is true  So  we will join both by OR because we want to select all if anyone of them is true        select   from emovis reporting     where  id 3 and cut name          and cut name       OR            id 3  and   cut name         or cut name

User · Answer

I just checked it in MySQL 5 7 and am really surprised how no one offered a simple answer  NATURAL JOIN When the tables or  select outcome  have IDENTICAL columns  you can use NATURAL JOIN as a way to find intersection   For example  table1  id  name  jobid  1    John    1   2    Jack    3   3    Adam    2   4    Bill    6  table2  id  name  jobid  1    John    1   2    Jack    3   3    Adam    2   4    Bill    5   5    Max    6  And here is the query  SELECT   FROM table1 NATURAL JOIN table2   Query Result  id  name  jobid  1    John    1   2    Jack    3   3    Adam    2

User · Answer

SELECT   campo1    campo2    campo3    campo4 FROM tabela1 WHERE CONCAT campo1 campo2 campo3 IF campo4 IS NULL    campo4   NOT IN  SELECT CONCAT campo1 campo2 campo3 IF campo4 IS NULL    campo4   FROM tabela2

[mysql] Alternative to Intersect in MySQL

Examples related to mysql

Examples related to intersect