MySQL의 Intersect 대안

MySQL의 Intersect 대안

---

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='恐慌') )

intersect가 MySQL에 없다는 것을 알고 있습니다. 그래서 다른 방법이 필요합니다. 나를 안내 해주세요.

---

Microsoft SQL Server의 "INTERSECT 피연산자의 왼쪽과 오른쪽에있는 쿼리에서 반환하는 고유 한 값을 반환합니다." 이것은 표준 또는 쿼리 와 다릅니다 .INTERSECT INNER JOINWHERE EXISTS

SQL 서버

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     |
+-------+

중복 된 값이 반환되지 않도록이 특정 질문으로, id 컬럼이 포함되어 있지만, 완전성을 위해, 여기에 사용하여 MySQL의 대안입니다 INNER JOIN및 DISTINCT:

SELECT DISTINCT value FROM table_a
INNER JOIN table_b
USING (value);

+-------+
| value |
+-------+
| B     |
+-------+

그리고 또 다른 예는 사용 WHERE ... IN과 DISTINCT:

SELECT DISTINCT value FROM table_a
WHERE (value) IN
(SELECT value FROM table_b);

+-------+
| value |
+-------+
| B     |
+-------+

---

UNION ALL 및 GROUP BY를 사용하여 교차를 생성하는 더 효과적인 방법이 있습니다. 대규모 데이터 세트에 대한 테스트에 따르면 성능이 두 배 더 좋습니다.

예:

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;

INNER JOIN 솔루션을 사용하면 MySQL이 첫 번째 쿼리의 결과를 찾은 다음 각 행에 대해 두 번째 쿼리의 결과를 찾기 때문에 더 효과적입니다. UNION ALL-GROUP BY 솔루션을 사용하면 첫 번째 쿼리의 결과, 두 번째 쿼리의 결과를 쿼리 한 다음 결과를 모두 한꺼번에 그룹화합니다.

---

로 cut_name= '全プロセス' and cut_name='恐慌'평가되지 않으므로 쿼리는 항상 빈 레코드 집합을 반환합니다 true.

일반적으로 INTERSECTin MySQL은 다음과 같이 에뮬레이션되어야합니다.

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
        )

---

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'

---

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.

---

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='恐慌') )

---

AFAIR, MySQL implements INTERSECT through INNER JOIN.

---

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);

참고URL : https://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql