Intersect Operator in MySQL

INTERSECT returns rows that occur in both query result sets. To use it in MySQL, first check whether your installed server accepts the native syntax; otherwise, an INNER JOIN, IN, or EXISTS query can express the overlap. Choose based on which columns you need and whether duplicate matches should appear.

How INTERSECT combines result sets

Think of each SELECT as producing a set of rows. INTERSECT keeps the rows shared by both sets, rather than returning every row from either side. The diagram highlights that shared region:

For example, intersecting customer IDs from a customer list and an order list means keeping customers who appear in both results. This is a set operation on the selected values. A join is a useful emulation when its join condition matches the values that define a row in the intersection. If you select different columns, you are combining related records rather than reproducing an intersection of identical rows.

Check INTERSECT support in your MySQL version

Support depends on the server version you actually run, so do not rely on a blanket statement or an assumed release threshold. Check the documentation for that installed version, or try a minimal query such as SELECT 1 INTERSECT SELECT 1; in a development session. If the server rejects the syntax, use one of the patterns below. The SQL snippets here are syntax examples; they require a MySQL server and were not executed in the supplied runtime.

Prepare the sample tables

The examples use customers and their orders. Name the table orders: ORDER is a SQL keyword, so using it as an unquoted table name is error-prone. Create customer with CREATE TABLE customer (customer_id INT PRIMARY KEY, customer_firstname VARCHAR(60) NOT NULL, customer_lastname VARCHAR(60) NOT NULL, customer_age INT NOT NULL, customer_salary INT NOT NULL);. Create orders with CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATETIME NOT NULL, order_amount INT NOT NULL, customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer(customer_id));.

Insert the sample customers with INSERT INTO customer VALUES (1, 'Shajeel', 'Daniel', 23, 9000), (2, 'Nayya', 'Preston', 54, 1500), (3, 'James', 'Robert', 36, 2500), (4, 'Jennifer', 'John', 29, 5000), (5, 'Sarah', 'Paul', 15, 8000), (6, 'Karen', 'Donald', 40, 3500);. Then insert the orders with INSERT INTO orders VALUES (1, '2019-12-03 10:25:30', 500, 2), (2, '2019-12-10 12:00:30', 1500, 4);; the order columns are order_id, order_date, order_amount, customer_id in that order.

Emulate INTERSECT with INNER JOIN, IN, and EXISTS

For an ordinary illustration of set overlap, this self-contained Python example keeps the IDs present in both input results. It is an executed Python illustration, not a MySQL query; its output is shown below. The SQL snippets are syntax examples and were not executed in the supplied runtime. Run this example:

customers = {1, 2, 3, 4}
ordered_customers = {2, 4, 6}
print(sorted(customers & ordered_customers))

[2, 4]

Use INNER JOIN when you need columns from both tables. The following syntax returns order details alongside matching customer details: SELECT o.order_id, c.customer_id, c.customer_firstname, c.customer_lastname, o.order_amount, o.order_date FROM orders AS o INNER JOIN customer AS c ON o.customer_id = c.customer_id;. For the sample rows, customer IDs 2 and 4 match. If a customer has several orders, this join returns one row for each matching order; it does not automatically have the one-row-per-customer behavior of a set operation.

Use IN when the result should contain customer columns and you only need to test membership in the other result: SELECT c.customer_id, c.customer_firstname, c.customer_lastname, c.customer_age FROM customer AS c WHERE c.customer_id IN (SELECT o.customer_id FROM orders AS o);. It returns customers 2 and 4 once each, even if either has multiple orders, because IN tests whether a matching value exists.

Use EXISTS for the same membership test when you also want a condition on the customer row: SELECT c.customer_id, c.customer_firstname, c.customer_lastname, c.customer_age FROM customer AS c WHERE c.customer_age < 45 AND EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);. With this sample, it returns customer 4; customer 2 is 54. EXISTS expresses the test directly and does not multiply a customer row when several orders match.

Choose the right emulation pattern

Choose INNER JOIN if the output needs columns from both sides or each matching pair matters. Choose IN for a concise membership filter whose output comes from one table. Choose EXISTS when the condition is naturally phrased as a correlated check, especially alongside other filters. If your join keys are not unique, a join can return repeated rows; use IN or EXISTS for membership, or deliberately apply DISTINCT when unique projected rows are required. Compare the selected columns and duplicate behavior with the result you expect from INTERSECT before adopting the query.