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.
Your query would always return an empty recordset since
cut_name= '全プロセス' and cut_name='恐慌'
will never evaluate totrue
.In general,
INTERSECT
inMySQL
should be emulated like this:If both your tables have columns marked as
NOT NULL
, you can omit theIS NULL
parts and rewrite the query with a slightly more efficientIN
:Break your problem in 2 statements: firstly, you want to select all if
is true . Secondly, you want to select all if
is true. So, we will join both by OR because we want to select all if anyone of them is true.
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 standardINNER JOIN
orWHERE EXISTS
query.SQL Server
MySQL
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
andDISTINCT
:And another example using
WHERE ... IN
andDISTINCT
: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:
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.
For completeness here is another method for emulating
INTERSECT
. Note that theIN (SELECT ...)
form suggested in other answers is generally more efficient.Generally for a table called
mytable
with a primary key calledid
:(Note that if you use
SELECT *
with this query you will get twice as many columns as are defined inmytable
, this is becauseINNER 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. TheWHERE
clause then filters thea
side of the pair, then theb
side. The result is that only rows which satisfy both conditions are returned, just like intersection two queries would do.