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.
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:
Query Result: id, name, jobid
'1', 'John', '1'
'2', 'Jack', '3'
'3', 'Adam', '2'
AFAIR, MySQL implements INTERSECT through INNER JOIN.