INTERSECT SQL Server statement

INTERSECT SQL Server statement – The statement is used to return 2 or more SELECT statements. However, it returns only the strings selected by all queries or data sets. If a record exists in one query and not in another, it will be omitted from the INTERSECT results.

INTERSECT query

The INTERSECT request will return the entries in the blue shaded area. These are records that exist in both SELECT1 and SELECT2.

Each operator in SQL INTERSECT must have the same number of fields in the result sets with the same data types.

Syntax of the INTERSECT operator in SQL

SELECT expr1, expr2, … expr_n
  FROM tabs
  [WHERE conds]
  INTERSECT
  SELECT expr1, expr2, … expr_n
  FROM tabs
  [WHERE conds];

Where:

• expr1,2,_n – Columns or calculations that you want to get.
• tabs – The tables from which you want the records. The FROM sentence must contain at least one table.
• WHERE conds – It’s optional. These are the conditions that must be met to select records.

Both SELECT operators must have the same number of expressions.

The corresponding expressions must have the same data type in SELECT operators. For example: expr1 must have the same data type in both the first and second SELECT operators.

Example – with one expression

Below is an example of an INTERSECT SQL statement that contains one field with the same data type:

SELECT supp_id
   FROM supps
INTERSECT
SELECT suppl_id
   FROM orders;

In this SQL example INTERSECT, if the suppl_id field is in the suppliers and orders tables, it will appear in your result set.

Now let us complicate our example by adding WHERE conditions to the INTERSECT query.

SELECT suppl_id
   FROM suppls
  WHERE suppl_id > 78
INTERSECT
SELECT suppl_id
   FROM orders
  WHERE quant <> 0;

Here, we can see that we have WHERE added to each data set. In the first dataset, the filtering was done in such a way that records from the suppl_id table were returned, where suppl_id is greater than 78. In the second data set, the filtering was performed so that only records from the order table were returned, where the number is necessarily not equal to 0.

Example – with several expressions

Next, let’s look at an example of using an INTERSECT SQL statement to return more than one column.

For example:

SELECT cont_id,
      l_name,
    f_name
   FROM conts
  WHERE l_name <> 'Marlos'
INTERSECT
SELECT cust_id,
     l_name,
    f_name
   FROM custs
  WHERE cust_id < 50;

In this example, the INTERSECT query will return records from the contacts table, where the values cont_id, l_name and f_name correspond to the values cust_id, last_name and f_name from the customers table.

The WHERE statement specifies the conditions for filtering results for each data set, only those records from the contacts table where l_name is not ‘Marlos’ are returned. Also records from the customers table where cust_id is less than 50.

Example – using ORDER BY

Below is an INTERSECT example using the ORDER BY clause:

SELECT suppl_id, suppl_name
   FROM suppls
  WHERE suppl_id > 2000
INTERSECT
SELECT com_id, com_name
   FROM coms
  WHERE com_id > 1000
ORDER BY 2;

Since the column names of the two SELECT operators are different, it is more advantageous to refer to the columns in the ORDER BY sentence by their position in the result set. In this example, we sort the results by suppl_name / com_name in ascending order, as denoted in ORDER BY 2.

The fields suppl_name / com_name are at position #2 in the result set.

Intersect operator in sql server