Home > Database > Mysql Tutorial > How Can I Replicate MySQL's `INTERSECT` Functionality?

How Can I Replicate MySQL's `INTERSECT` Functionality?

Linda Hamilton
Release: 2024-12-22 12:58:36
Original
814 people have browsed it

How Can I Replicate MySQL's `INTERSECT` Functionality?

Finding Equivalents for MySQL's Intersect

In SQL, the INTERSECT operator retrieves distinct values that appear in both specified queries. However, MySQL does not natively support INTERSECT.

Alternatives in MySQL

To achieve a similar result in MySQL, one can use:

  • INNER JOIN: Per column values match between both tables, but duplicates will be returned.
  • WHERE ... IN: Selects distinct values within the first table that also exist within the second table.

Example

Consider the following query in another database management system that utilizes INTERSECT:

(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='恐慌') )
Copy after login

MySQL Alternative using INNER JOIN:

SELECT DISTINCT * 
FROM emovis_reporting e1
INNER JOIN emovis_reporting e2 
ON (e1.id = e2.id) AND (e1.cut_name = e2.cut_name)
WHERE e1.id = 3 AND (e1.cut_name = '全プロセス' OR e1.cut_name = '恐慌');
Copy after login

MySQL Alternative using WHERE ... IN:

SELECT DISTINCT * 
FROM emovis_reporting
WHERE (id = 3) AND (cut_name IN ('全プロセス', '恐慌'));
Copy after login

These MySQL alternatives return distinct values that satisfy the conditions specified in both subqueries. However, it's important to note that the id column is involved in this specific query, which will prevent duplicates from being returned. For queries without unique identifiers, using DISTINCT in the outer query may be necessary to ensure unique results.

The above is the detailed content of How Can I Replicate MySQL's `INTERSECT` Functionality?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template