Loading [MathJax]/jax/output/HTML-CSS/config.js

riven

Riven

Riven

What is SQL INTERSECT?

The INTERSECT operator combines the results of two or more SELECT statements and returns only the rows that are common to both result sets. Like the UNION operator, INTERSECT requires that the SELECT statements involved must have the same number of columns and compatible data types.

Syntax

The basic syntax of the INTERSECT operator is as follows:

SELECT column1, column2, ...
FROM table1
WHERE condition1

INTERSECT

SELECT column1, column2, ...
FROM table2
WHERE condition2;

Important Points

  1. Same Number of Columns: Each SELECT statement must have the same number of columns in the result sets, and the columns must have compatible data types.

  2. Distinct Rows: By default, INTERSECT returns only distinct rows. If there are duplicate rows in the result sets, they will appear only once in the final result.

  3. Column Names: The column names in the result set are taken from the first SELECT statement.

Example Scenario

Let’s consider two tables: Employees and Contractors.

Table 1: Employees

sql intersect example

Table 2: Contractors

Example 1: Basic INTERSECT

Let’s say we want to find the names of individuals who are present in both the Employees and Contractors tables.

SELECT Name FROM Employees
INTERSECT
SELECT Name FROM Contractors;
Result:

In this case, both Bob and Charlie are present in both tables.

Example 2: Using Additional Columns

If we want to find the common records based on both name and department, we can extend the previous example:

SELECT Name, Department FROM Employees
INTERSECT
SELECT Name, Department FROM Contractors;
Result:

In this case, the result will still show the common entries with respect to both columns.

Example 3: Filtering with WHERE Clauses

You can also apply WHERE clauses to filter the results before performing the intersection. For example, if you want to find contractors who are also employees in the IT department:

SELECT Name FROM Employees WHERE Department = 'IT'
INTERSECT
SELECT Name FROM Contractors WHERE Department = 'IT';
Result :

In this case, Bob is the only one who is an employee and contractor in the IT department.

Performance Considerations

  1. Efficiency: The INTERSECT operation can be less efficient than a single SELECT query with a JOIN if you’re working with large datasets. Depending on your specific use case, consider whether a JOIN might be more suitable.

  2. Indexes: Indexes can help improve the performance of queries that use INTERSECT, especially when filtering large tables.

  3. Distinct Rows: Since INTERSECT inherently removes duplicates, it can sometimes add overhead to query performance.

Use Cases

  1. Finding Common Data: INTERSECT is ideal for scenarios where you need to find common records between two datasets, such as employees and contractors, or products in stock across different warehouses.

  2. Data Validation: It can be used to validate records across different tables to ensure consistency, such as checking if certain users exist in both a users table and an orders table.

  3. Complex Reporting: In reporting scenarios, INTERSECT can help combine data from multiple sources to provide a comprehensive view.

Previous
Next
SQL-Grouping by
SQL-GROUP BY  The GROUP BY clause in SQL is an essential tool for aggregating data across multiple rows...
SQL-HAVING clause
SQL- HAVING clause The HAVING clause in SQL is an essential tool for filtering results after grouping...
INSERT SQL
What is insert SQL The SQL INSERT statement is one of the fundamental operations in SQL, enabling users...
SQL-SELF JOIN
SQL SELF JOIN A self join is a type of join where a table is joined with itself. This can be particularly...
SQL-Stored Procedure
SQL stored procedure   What is  Stored Procedure? A stored procedure is a set of SQL statements that...
Network Model
Network model (DBMS) The network model is a data model that organizes data in a graph structure, where...
SQL RIGHT JOIN
SQL RIGHT JOIN A RIGHT JOIN, also known as a RIGHT OUTER JOIN, is a type of join that returns all records...
SQL-INTERSECT
What is SQL INTERSECT? The INTERSECT operator combines the results of two or more SELECT statements and...
NoSQL databases
What is NOSQL? NoSQL databases are a category of database management systems that do not adhere to the...
SQL CROSS JOIN
SQL CROSS JOIN  A CROSS JOIN is a type of join that returns the Cartesian product of two tables. The...