Text copied!
INTERSECT operator
In SQL, Operators are special character, symbol or a keyword that is used to perform some specific operations. For example : comparing data
SQL operators are commonly used with SQL statements to retrieve, filter, and manipulate data.
INTERSECT operator :
INTERSECT operator is used to retrieve the matching rows from two or more SELECT statements. It returns only distinct rows as result.
The syntax of the INTERSECT operator generally looks like this :
SELECT column_name(s)
FROM table1
INTERSECT
SELECT column_name(s)
FROM table2;
• Specify the column(s) name after the 'SELECT' keyword.
• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.
• Specify the table name after the 'FROM' keyword.
• 'table1' and 'table2' refer to the two tables being merged together.
• 'INTERSECT' keyword combines the result set of multiple SELECT statements into a single result and returns distinct rows.
Here's an example of how you might use the INTERSECT operator :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].
[b] Employees_2 Table :
→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.
1. Please ensure that each column_name(s) and their data types are in the same order across all SELECT statements.
For example, let's see what happens if we run the below statement :
SELECT *
FROM [Employees]
INTERSECT
SELECT *
FROM [Employees_2];
An error is thrown indicating that while performing 'INTERSECT' operation, number of columns must be the same in all SELECT statements.
The error occurred because we specified 'Asterisk' (*) symbol that selects all columns from the table where [Employees_2] table contains '[Age]' column which is absent in [Employees] table. Therefore, we need to specify column_name(s) explicitly in all SELECT statements.
2. Please run the following SQL statement :
SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees]
INTERSECT
SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees_2];
3. Above statement merges the result sets of both employee tables into a single result set for all selected columns. It returns distinct and only common rows present in both the table. Hence only 1 row is returned.
That's it! You have successfully merged data using 'INTERSECT' operator.