mysql中替代 =_MySQL中相交的替代方法

Microsoft SQL Server的INTERSECT “返回由IntersectOperand左侧和右侧的查询返回的任何不同值”这与标准不同INNER JOIN或WHERE EXISTS查询。

SQLServer

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     |

+-------+