Sql INTERSECT Operator

The SQL INTERSECT operator is used to return the rows that are in common between two result sets.

If a row exists in one result set and not in the other, it will be ignored or omitted by the operator in the final results.

The two queries must result in the same number of columns and compatible data types in order to combine.

• There must be same number of columns and order in both SQL SELECT statements.
• The corresponding columns in each of the SELECT statements must have similar or compatiable data types.

The SQL INTERSECT operator returns the results of two or more SELECT queries; in this case, the INTERSECT query returns the entries in the blue shaded area. These are the records that both Dataset1 and Dataset2 have. It only outputs the rows chosen by all queries or data sets, though.

INTERSECT DISTINCT and INTERSECT are the same thing. We can use the SQL intersect operator to find common values between two tables or views.

Each SQL statement within the SQL INTERSECT must have the same number of fields with equivalent data types.

When employing the INTERSECT operator, the same rules apply as when using the UNION operator. The INTERSECT operator is not supported by MySQL.

A record will be omitted from the INTERSECT results if it exists in one query but not the other.

The intersection of A and B (A ∩  B) is the group in mathematics that consists of all members of A that also correspond to B.

Syntax:

The basic syntax of INTERSECT is as follows.

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

INTERSECT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here, the given condition could be any given expression based on your requirement.

Example 1:

SELECT  ID, NAME, Amount, Date
 FROM Customers
 LEFT JOIN Orders
 ON Customers.ID = Orders.Customer_id
INTERSECT
 SELECT  ID, NAME, Amount, Date
 FROM Customers
 RIGHT JOIN Orders
 ON Customers.ID = Orders.Customer_id;

Example 2: Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows

Table 2 − ORDERS Table is as follows.

Now, let us join these two tables in our SELECT statement as follows.

SELECT  ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 LEFT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INTERSECT
 SELECT  ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 RIGHT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

Example 3: Currencykey is a common column in both tables; we'll evaluate them and look for common values using this query:

select Currencykey from [dbo].[FactInternetSales]
intersect 
select currencykey from DimCurrency

Example 4: The following is a SQL INTERSECT operator example that has one field with the same data type:

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

If a supplier_id occurred in both the suppliers and orders tables in this SQL INTERSECT example, it would appear in your result set.

INTERSECT ALL returns the common row but does not remove duplicates.

The INTERSECT ALL returns the set of matching individual rows. An INTERSECT and/or EXCEPT operation is done by matching ALL of the columns in the top and bottom result sets. In other words, these are rows, not the column, operations. It is not possible to only match on the keys, yet at the same time, also fetch non-key columns.

The intersection between the result sets of several SELECT statements can be obtained as a single result using INTERSECT or INTERSECT ALL.

The select list in each of the component SELECT statements must have the same number of elements, and ORDER BY clauses are not allowed.

INTERSECT produces the identical results as INTERSECT ALL, with the exception that duplicate rows are removed before the intersection of the result sets is calculated.

The column names that appear are the same as those that appear in the first SELECT statement. Using the WITH clause on the SELECT statement as an alternative to modifying result set column names.

INTERSECT ALL Syntax :

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Example 1: Let’s discuss with an example. Let’s take two tables for example:

SELECT * FROM SummerFruits
INTERSECT ALL
SELECT * FROM Fruits

INTERSECT ALL gets the common row from both table does not remove duplicates so resultant table.

Example 2: Let’s consider below example

The query,

SELECT R1
FROM R1
INTERSECT ALL 
SELECT R2
FROM R2
ORDER BY 1;

Output:

Example 3: Let’s try that by creating our 2 tables again, and by inserting some sample rows.

declare @t1 TABLE (
Col1 INT,
Col2 INT,
Col3 INT
)

declare @t2 TABLE (
Col1 INT,
Col2 INT
)

INSERT INTO @t1 VALUES (1, 1, 1), (2, 2, 2), (2, 2, 2), (3, 3, 3)
INSERT INTO @t2 VALUES (2, 2), (2, 1), (2, 2), (3, 3)

;WITH IntersectAll AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY (SELECT 0)) AS RowNumber,
Col1,
Col2
FROM @t1

INTERSECT

SELECT
ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY (SELECT 0)) AS RowNumber,
Col1,
Col2
FROM @t2
)
SELECT Col1, Col2 FROM IntersectAll

The EXCEPT and INTERSECT operators were used to merge the result sets obtained by two searches in order to retrieve the desired result.

With COMPUTE and COMPUTE BY clauses, we can't use EXCEPT and INTERSECT in global partitioned view declarations.

Fast forward-only and static cursors, as well as distributed queries, can employ EXCEPT and INTERSECT, but only on the local server. They can't be run on a remote server.

INTERSECT – Set Operator

Only similar unique rows from various result sets are combined using the INTERSECT operator. If a row is included in both the input and output result sets, it is included in the final result set. If a row appears in one set of results but not in another, it is eliminated.

Duplicate rows are also eliminated. A single duplicated row example is returned. Most importantly, the INTERSECT operator treats NULLs similarly, so they are concatenated.

EXCEPT – Set Operator

The EXCEPT operator removes rows from the first result set that are not present in the second. Rows from the first result set that don't match the second result set are removed by default. Furthermore, using the EXCEPT operator, duplicate rows will be removed from the final result set.

With SET operators, nulls are interpreted in the opposite way. They get the same treatment. As a result, if both result sets contain NULLs, INTERSECT will return NULLs because their existence is common in both. This is something to keep in mind.

Example 1: Table dbo.Students and dbo.FootBallTeam has duplicate rows for student id 5. If we run EXCEPT and INTERSECT examples then duplicate values are not returned. Student Id 5 is returned only once even though it’s duplicated.

SELECT Id FROM dbo.Students
EXCEPT
SELECT StudentId FROM dbo.FootballTeam;

Output:

SELECT Id FROM dbo.Students
INTERSECT
SELECT StudentId FROM dbo.FootballTeam;

Output:

Example 2: Because these entries are common between two result sets, NULLs are presented alongside 1,2 in the instance below.

SELECT * FROM (VALUES (NULL, NULL), (2,3), (1,2)) as Test1(Col1,Col2)
INTERSECT
SELECT * FROM (VALUES (NULL, NULL), (1,2)) as Test2(Col1,Col2);

Output:

If NULLs are available in the first result set but not in the second, the EXCEPT operator is used. As a result, NULLs appear in the result set.

SELECT * FROM (VALUES (NULL, NULL), (2,3), (1,2)) as Test1(Col1,Col2)
EXCEPT
SELECT * FROM (VALUES (1,2)) as Test2(Col1,Col2);

Output:

The intersect table was created to combine the data from both tables. Given the resultant data, count the data.

Example 1:

SELECT COUNT(COLA) as myCount FROM (SELECT ColA 
FROM @table1 
INTERSECT
SELECT COlB
FROM @table2 ) t

--Or

WIth mycte As 
(
SELECT ColA 
FROM @table1 
INTERSECT
SELECT COlB
FROM @table2 

)
SELECT COUNT(*) as myCount FROM myCTE

Example 2:

SELECT COUNT(*) FROM
(
 SELECT id FROM table1 where  col1 like '%abcd%'
 intersect
 SELECT id from table2 where col2 like '%efgh%'
) 

Where I is an alias for the table 'derived.' It serves no purpose in this case, but it is required for SQL to recognise the syntax; otherwise, you will receive a "Incorrect syntax near ')'" error.

INTERSECT or INTERSECT ALL can be used to combine the result sets of numerous SELECT statements into a single result. INTERSECT DISTINCT and INTERSECT are the same thing.

Syntax:

select-statement
  INTERSECT [ ALL | DISTINCT ] select-statement
[ INTERSECT [ ALL | DISTINCT ] select-statement ] ...
[ ORDER BY integer [ ASC | DESC ], ... ]

Parameters

select_statement1 and select_statement2: required. These parameters specify the SELECT clauses.

distinct: optional. This parameter is used to remove duplicate values from the intersection of two datasets.

Example 1:

INTERSECT DISTINCT
@intersect_distinct =
    SELECT * FROM @a
    INTERSECT DISTINCT
    SELECT * FROM @b;
@intersect_distinct

Output:

Example 2: The intersection does not contain duplicate values. Sample statements:

select * from values (1, 2), (1, 2), (3, 4), (5, 6) t(a, b) 
intersect distinct 
select * from values (1, 2), (1, 2), (3, 4), (5, 7) t(a, b);

GROUP BY reduces row counts, whereas JOIN increases row counts. Because GROUP BY clauses should be evaluated before FROM and WHERE clauses. By substituting a join with a set operator, you can make it happen afterwards. (The set operators in SQL are UNION, EXCEPT, and INTERSECT.)

Example 1: For example, replace Statements:

SELECT column1, SUM(column2), 0 FROM Table1 GROUP BY column1 
INTERSECT 
SELECT column1, 0, SUM(column2) FROM Table2

Example 2: Choose groups from set 1 for which no groups exist in set 2 and for which all items from set 1 are present in set2:

select s1.grp from set1 s1
where not exists(
 select * from set2 s2 where not exists(
 select item from set1 s11 
 where s11.grp = s1.grp 
 intersect 
 select item from set2 s22
 where s22.grp = s2.grp))
group by s1.grp

From the left and right queries, the intersect operator will return all unique rows. WHERE clause can be used with any or all intersect queries.

The syntax for the INTERSECT operator with Where caluse in SQL is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

WHERE conditions Optional. These are the criteria that must be met in order for the recordings to be chosen.

Example 1: Adding WHERE conditions to the INTERSECT query.

SELECT supplier_id
FROM suppliers
WHERE supply_id > 78
INTERSECT
SELECT supply_id
FROM orders
WHERE quantity <> 0;

The WHERE clauses have been added to each of the datasets in this instance. Only records from the suppliers table with a supply_id larger than 78 have been returned in the first dataset. Only records from the orders database with a quantity greater than zero are returned in the second dataset.

Example 2: use this operator along with the WHERE Clause. In this example, we are combining two statements:

The first SQL Server result set pulls records from Employ that have a yearly income of at least $700,000.

The second set of results pulls all of the data from the [Employees 2016] table.

This operator also chooses all entries that are common in both the first and second result sets.

SELECT [ID] ,[FirstName]
,[LastName]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Employ]
WHERE [YearlyIncome] >= 70000

 INTERSECT

SELECT [ID]
,[FirstName]
,[LastName]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Employees 2016]

Example 3: Only the positions of receptionist and business development do not have male and female personnel. Let's see if the INTERSECT operator can successfully retrieve the results.

SELECT title
FROM HR
WHERE Gender = 'M'

INTERSECT

SELECT title
FROM HR
WHERE Gender = 'F'

When a SQL Select statement has several columns, the Intersect operator compares values from the first and second select statements using all of the columns from the first select statement.

Syntax for Intersect operator with multiple columns in Select statement.

SELECT dept_id, dept_name
FROM table1

INTERSECT

SELECT dept_id, dept_name
FROM table2

The SQLite Intersect operation will return entries from table1 that do not match the values of the dept_id and dept_name columns in table2.

Example 1:

Table 1: Product table

Table 2: Inventory table

Using INTERSECT clause with multiple columns: The following SQL code can be used to get the records of the Name and Brand columns that are contained in both record sets:

SELECT Name, Brand FROM Product
INTERSECT
SELECT Name, Brand FROM Inventory
ORDER BY Name;

Output:

Example 2:

employees - All of the employees employed at our company.

planning_committee - All of the employees on our company’s planning committee.

INTERSECT clause behaves in this scenario.

SELECT employee_id, first_name, last_name
FROM employees
INTERSECT
SELECT employee_id, first_name, last_name
FROM planning_committee

Output:

You may also get the data from the table with instersect.

Example 1: Consider the following three tables:

Company_A

Company_B

Company_C

query returns the ID and last name of the employee who works for all three companies:

SELECT id, emp_lname FROM Company_A
INTERSECT
SELECT id, emp_lname FROM Company_B
INTERSECT
SELECT id, emp_lname FROM Company_C;

Output:

Example 2:

select Currencykey from [dbo].[FactInternetSales]
intersect 
select currencykey from DimCurrency
intersect 
select currencykey from dbo.table1
intersect 
select currencykey from dbo.table2

This instance will display all of the dbo tables common currency keys. table1, table2, facinternetsales, dimcurrency.

Both the left and right SELECT statements' results sets contain the rows returned by INTERSECT. Because INTERSECT removes duplicate rows, the resulting rows are always distinct or distinctive.

INNER JOIN will not include those rows in the result-set if both tables contain NULLs in the joining field, but INTERSECT regards two NULLs as the same value and returns all corresponding rows.

Some Null fields in Columns that intersect will include that row in the Result, but Inner Joins and Exists predicates both filter out Null Values.

You must create additional code to include nulls in your output when using inner join and Existing Predicate.

"NULL, but not NULL, thank you SQL," it says. The trendy one But wait! When it comes to NULL, SQL is incredibly unreliable. These set operations, like UNION and EXCEPT (MINUS) in Oracle, as well as SELECT DISTINCT, treat two NULL entries as NOT DISTINCT.

Example 1: To check intersect with Nulls I am inserting Rows same Rows in both tables:

insert into Employee1 values(null,'5000')
insert into Employee1 values(null,'5000')
insert into Employee1 values(null,'5000')

The reason I added the data three times is because both tables contain an auto-incrementent column, but employee 1 only has five entries while employee 2 has seven, so we need to match Empid as well to make both rows equivalent.

insert into Employee2 values(null,'5000')

Now we are Running the Intersection query again

select * from Employee1
intersect
select * from Employee2

Output:

Example 2: Consider the following example, where the table t1 has following rows:

create table t1 (col1 int);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (4);
insert into t1 values (NULL);
insert into t1 values (NULL);
insert into t1 values (NULL);

In the same example, table t2 has these rows:

create table t2 (col1 int);
insert into t2 values (1);
insert into t2 values (3);
insert into t2 values (4);
insert into t2 values (4);
insert into t2 values (NULL);

On the left and right sides of the INTERSECT operand, the preceding query returns the separate rows from both the query and the INTERSECT operand. The crucial thing to remember is that the outcome is NULL. Because the NULL value in table t2 is regarded equal when table t2 is compared to table t1, the combined result set returns a NULL value from the group intersection:

SELECT col1 FROM t1 INTERSECT SELECT col1 FROM t2;

Output:

Example 3: "NULL, but not NULL, thank you SQL," it says. The trendy one But wait! When it comes to NULL, SQL is incredibly unreliable. In three-valued logic, NULL actually implies UNKNOWN, which means we never know if SQL follows its own rules. Enter INTERSECT. These set operations, like Oracle's UNION and EXCEPT (MINUS), as well as SELECT DISTINCT, treat two NULL entries as NOT DISTINCT. They aren't equal, but neither are they unlike. Whatever. Remember: That's the way it is:) As a result, we can write Rafael's hipster approach:

WITH t(a, b, c) AS (...)
SELECT *
FROM t
WHERE EXISTS (
  SELECT a, b, c FROM dual
  INTERSECT (
  SELECT 'a', 'b', null FROM dual
  UNION ALL
  SELECT 'a', null, 'c' FROM dual
  )
)

We finish by creating an intersection of the tuple (a, b, c), Rafael's IN predicate's left side, and the desired values on the right side of the IN predicate.

Example 1: I looked up examples of the aforementioned procedure in MySQL and only found two tables involved. When attempting to get a MySQL command to work, I've yet to get it to work without throwing an error.

SELECT ID
FROM Testdb.Testtable
WHERE ID = "105";

INTERSECT

SELECT ID
FROM Testdb.Testtable
WHERE ID = "255";

INTERSECT

SELECT ID
FROM Testdb.Testtable
WHERE ID = "500";

Example 2:

SELECT id_user 
  FROM Rating 
 Where id_movie=2 
INTERSECT 
SELECT id_user 
  FROM Rating 
 Where id_movie=3;

but I get:

Your SQL syntax is incorrect; see the manual for your MySQL server version to find the correct syntax. 'INTERSECT SELECT id_user FROM Rating Where id_movie=3 LIMIT 0, 30' at line 1

The ORDER BY clause is placed at the conclusion of all statements to sort the result set given by the INTERSECT operator.

Example 1: The INTERSECT operation is applied to the A and B tables, and the merged result set is sorted in descending order by the id column.

SELECT
   id
FROM
   a 
INTERSECT
SELECT
   id
FROM
   b
ORDER BY id DESC;

Example 2: INTERSECT example that uses a ORDER BY clause:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 2000
INTERSECT
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

Because the column names in the two SELECT statements are different, it's easier to relate to the columns in the ORDER BY clause by their position in the result set. As seen by the ORDER BY 2, we've categorized the results in ascending order by supplier_name / company_name.

Example 3: use the integer that represents the position of the sales column (3) to return the same result:

SELECT id, emp_lname, sales FROM Company_A
   INTERSECT
SELECT id, emp_lname, sales FROM Company_B
ORDER BY 3 DESC;

Output:

The following query returns the employee who works for both companies whose sales in Company_B are greater than 1000:

SELECT id, emp_lname, sales FROM Company_A
INTERSECT
(SELECT id, emp_lname, sales FROM company_B WHERE sales > 1000)
ORDER BY sales DESC;

Output:

In MS Access, it intersects numerous subqueries. Apparently, the INTERSECT keyword is not supported by Access-SQL. I realise how we can acquire the desired intersection by using the INNER JOIN of two tables.

Syntax uses the IN and Subquery clause for returning the distinct rows from both tables:

SELECT DISTINCT column_list FROM table_name1  
WHERE column_name IN (SELECT column_list FROM table_name2);

or

SELECT DISTINCT Id FROM tab1  
WHERE Id IN (SELECT Id FROM tab2);  

Example: I have two tables: Employees and specialisations. Each employee can have numerous specialisations, implying a many-to-many relationship between Employees and specialisations, which is implemented by an extra table containing employee and specialty ids.

Let's assume I need a list of employees with all of their expertise listed. In any other SQL engine, I would basically make a list of subqueries and use the INTERSECTION term to "connect" them together, resulting in something like this:

SELECT * FROM ( 
 (SELECT id, first_name, last_name FROM Employees JOIN Emp_spec 
 ON Employee.id = Emp_spec.spec_id WHERE Emp_spec.spec_id=x_1 )
 INTERSECT 
 ... 
 INTERSECT
 (SELECT id, first_name, last_name FROM Employees JOIN Emp_spec 
 ON Employee.id = Emp_spec.spec_id WHERE Emp_spec.spec_id=x_n )
);

Where x_1,...,x_n represent some ids corresponding to some specializations. This query returns a set of employees, all of which have all the specializations x_1,...,x_n. So how do I create such query in Access without the INTERSECT keyword. I've been trying to write the equivalent query with INNER JOIN but I can't seem to succeed.

EXISTS evaluates to true if INTERSECT delivers a row, which causes WHERE to evaluate to true.

EXISTS returns false if INTERSECT returns no rows, making the WHERE false.

Syntax to use INTERSECT, duplicates will be eliminated:

SELECT a.CustID FROM tbl1 AS a
INTERSECT
SELECT b.CustID FROM tbl2 AS b

Example: Following query will result multiple rows if you have duplicate CustId in tbl1

SELECT a.CustID FROM tbl1 AS a 
WHERE EXISTS (SELECT b.CustID FROM tbl2 b WHERE (a.CustID=b.CustID))

In many instances, the INTERSECT operator produces essentially identical results as the INNER JOIN clause. The INTERSECT operator is used to retrieve common records from the Intersect Operator's left and right queries. In many instances, the INTERSECT operator produces essentially identical results as the INNER JOIN clause.

Inner join returns duplicate values if they exist in the tables, whereas INTERSECT returns NULL.

The number and order of columns in all queries must be the same when employing the INTERSECT operator, and the data types must be suitable.

An inner join is a sort of join that uses the operator intersect. Inner join cannot return matching null values, while intersect can.

The two are significantly different; one is a match-on-a-limited-set-of-columns operator that can yield zero or more rows in either table.

In general, INNER JOIN is quicker in general since it returns only the rows that match in all joined tables depending on the joined column. So, despite the fact that they both return the same number of rows, INNER JOIN is quicker.

Example 1: Based on performing the identical query 100000 times, I believe intersect is more performant than the inner join:

SELECT Summary.ID
FROM Summary
INNER JOIN Detail
ON Summary.ID = Detail.ID

and

SELECT ID FROM Summary
INTERSECT
SELECT ID FROM Detail

Output:

Example 2: To emulate the SQL INTERSECT operator, you can use the INNER JOIN clause as follows:

SELECT
	a.id
FROM
	a
INNER JOIN b ON b.id = a.id

It yields the rows in the A table that have matching rows in the B table, similar to the INTERSECT operator.

You should now be familiar with the SQL INTERSECT operator and how to use it to locate the intersections of multiple queries.

Example 3: Let's look at the relationship between INTERSECT and INNER JOIN. To illustrate our example, we will use the AdventureWorks database.

Simple Example of INTERSECT

SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (1,2,3)
INTERSECT
SELECT *
FROM HumanResources.EmployeeDepartmentHistory
WHERE EmployeeID IN (3,2,5)

Using INNER JOIN

SELECT va.VendorID,va.ModifiedDate
FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID
AND vc.ModifiedDate = va.ModifiedDate

Example: consider the two tables for get the results for Intersect / Minus

Employee_OBIEE

Table Name:Employee_Cognos

Intersect Query:

Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Intersect
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;

Output:

The above Query should fetch only the common records:

Minus Query

Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Minus
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;

Output:

It has eliminated the record of Employee named Rohit.