SQL Intersect

How does the INTERSECT operator work in SQL?

The SQL INTERSECT operator returns only the rows that are common to two or more SELECT statements. It's a powerful tool for comparing data sets and finding overlapping results.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The INTERSECT operator in SQL is used to find the common rows between two or more SELECT statements. It's similar to set intersection in mathematics. Imagine you have two tables, and you want to identify the records that exist in both. INTERSECT helps you do exactly that. Crucially, the result set contains only the rows present in *all* the input SELECT statements. The order of the columns in the result set is determined by the first SELECT statement. INTERSECT is particularly useful when you need to compare data from different sources or identify matching records across multiple tables. It's important to note that the columns in the SELECT statements must have compatible data types for the INTERSECT operation to work correctly. For example, you can't INTERSECT a column of integers with a column of strings.

Why SQL Intersect is important

The INTERSECT operator is important because it allows for efficient comparison of data sets. It's a concise way to identify common elements between different result sets, which is crucial in data analysis, reporting, and data integration tasks.

SQL Intersect Example Usage


-- Sample table: Sales
CREATE TABLE Sales (
    Department VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO Sales (Department, SalesAmount) VALUES
('Electronics', 15000),
('Electronics', 12000),
('Clothing', 8000),
('Clothing', 9500),
('Clothing', 10000),
('Furniture', 11000),
('Furniture', 12500);

-- Calculate the average sales for each department and filter those with average sales above $10,000
SELECT Department, AVG(SalesAmount) AS AverageSales
FROM Sales
GROUP BY Department
HAVING AVG(SalesAmount) > 10000;

Common Mistakes

Forgetting to ensure compatible data types in the SELECT statements.
Misunderstanding that INTERSECT only works with two SELECT statements; it can be used with more than two.
Assuming the order of columns in the result set is preserved from each SELECT statement; it's determined by the first SELECT.

SQL Intersect

How does the INTERSECT operator work in SQL?

Description

Why SQL Intersect is important

SQL Intersect Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL