As the name suggests, the intersect statement in SQL allows you to combine two select statements. It works by combining two select statements and returning the identical rows in both select statements. Hence, it enables you to fetch matching rows from two select statements.
Syntax
The syntax for the intersect statement is as shown below:
SELECT col1 , col2, …colN
FROM TABLE_NAME
WHERE condition
INTERSECT
SELECT col1, col2,…colN
FROM TABLE_NAME
WHERE condition
FROM TABLE_NAME
WHERE condition
INTERSECT
SELECT col1, col2,…colN
FROM TABLE_NAME
WHERE condition
The where clause in the above query is optional. However, it can allow you to narrow down for specific records in the table.
Example
Suppose we have two tables as shown:
Table 1
Table 2
We can perform an intersect operation as shown:
SELECT customer_id, first_name, email, activebool
FROM customer
LEFT JOIN store
ON customer.store_id = store.store_id
INTERSECT
SELECT customer_id, first_name, email, activebool
FROM customer
RIGHT JOIN store
ON customer.store_id = store.store_id
ORDER BY customer_id;
FROM customer
LEFT JOIN store
ON customer.store_id = store.store_id
INTERSECT
SELECT customer_id, first_name, email, activebool
FROM customer
RIGHT JOIN store
ON customer.store_id = store.store_id
ORDER BY customer_id;
The query above should intersect the result of the first select statement with the second.
This should return:
Conclusion
This article discusses the intersect query in SQL. Keep in mind that specific database engines such as MySQL do not support the intersect clause.
