SQL Standard

SQL Intersect

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

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;

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list