Basic about INTERSECT Operator – using and example

This SQL tutorial explains how to use the SQL INTERSECT operator with syntax and examples.

Description

The SQL INTERSECT operator is used to return the consequences of two or extra SELECT statements. However, it solely returns the rows chosen through all queries or records sets. If a file exists in one query and not in the other, it will be ignored from the INTERSECT results.

Intersect Query

Explanation: The INTERSECT question will return the information in the blue shaded area. These are the documents that exist in each Dataset1 and Dataset2.

Each SQL assertion inside the SQL INTERSECT have to have the equal quantity of fields in the result units with comparable information types.

Syntax

The syntax for the INTERSECT operator in SQL is:

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

Parameters or Arguments

expression1, expression2, expression_n The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve archives from. There need to be at least one desk listed in the FROM clause. WHERE conditions Optional. These are prerequisites that need to be met for the files to be selected.

Note

There should be same variety of expressions in both SELECT statements. The corresponding expressions have to have the same data type in the SELECT statements. For example: expression1 have to be the equal facts kind in each the first and 2nd SELECT statement.

Example – With Single Expression

The following is a SQL INTERSECT operator example that has one discipline with the equal records type:

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

In this SQL INTERSECT example, if a supplier_id appeared in each the suppliers and orders table, it would show up in your end result set.

Now, let’s complicate our example similarly through including WHERE stipulations to the INTERSECT query.

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

In this example, the WHERE clauses have been introduced to each of the datasets. The first dataset has been filtered so that solely information from the suppliers table where the supplier_id is increased than seventy eight are returned. The second dataset has been filtered so that only documents from the orders desk are returned the place the quantity is now not equal to 0

Example – With Multiple Expressions

Next, let’s seem to be at an example of how to use the INTERSECT operator in SQL to return greater than one column.

For example:

SELECT contact_id, last_name, first_name
FROM contacts
WHERE last_name <> 'Anderson'
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE customer_id < 50;

In this INTERSECT example, the query will return the records from the contacts table where the contact_id, last_name, and first_name values healthy the customer_id, last_name, and first_name fee from the customers table.

There are WHERE conditions on every statistics set to similarly filter the consequences so that solely archives from the contacts are again where the last_name is no longer Anderson. The records from the customers table are back the place the customer_id is much less than 50.

Example – Using ORDER BY

The following is an 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;

Since the column names are extraordinary between the two SELECT statements, it is greater fine to reference the columns in the ORDER BY clause by way of their role in the end result set. In this example, we have sorted the outcomes with the aid of supplier_name / company_name in ascending order, as denoted with the aid of the ORDER BY two

The supplier_name / company_name fields are in function #2 in the end result set.