INTERSECT Teradata

Compatibility: ANSI

The INTERSECT set operator is used to match or join the common domain values from two or more sets. When using the INTERSECT, all SELECT statements must have the same number of columns and all columns will be used in the join operation. This should provoke the thought that columns such as a person's name will probably not be used. The join performed is equivalent of an INNER JOIN. By default, only the identical row from the multiple answer sets will be returned.

Since SET operators are normally used to create a single desired answer set, the INTERSECT is commonly used to prepare for subsequent processing. In other words, the rows from the INTERSECT become the catalyst set used to join with another table to return the detail data rows that are actually needed.

The syntax of the INTERSECT:

The following INTERSECT could be used to find all of the departmental managers:

4 Rows Returned

Manager

1000234
1121334
1256349

Manager

1333454

As obvious as this might appear, it contains some subtle traps. There are four rows output, but five different departments. In viewing this output, it appears that no manager is responsible for more than one department and one department does not have a manager. However, this could be a misconception.

Earlier, the considerations section indicated that SET operators eliminate duplicate rows. Therefore, if a manager's employee number were selected more than once, the SET operator throws away all duplicates and returns only one row.

In order to see the duplicates, the process of eliminating them must be turned off. This is easily accomplished by coding the above statement as an INTERSECT ALL. As a matter of fact the keyword ALL actually speeds up the set operator queries because Teradata does not have to look for and eliminate duplicates.

Anytime you are using SET operators and you know the information has no duplicates you should use the ALL keyword to speed up the request processing. Because we did not use the ALL keyword in our last example, the output contains the manager's employee number only once.

Therefore, the following is another way to determine all departmental managers using the INTERSECT ALL to prevent the identical employee numbers from being eliminated:


5 Rows Returned

Manager

1000234
1121334
1256349
1256349
1333454

Now, it is known that one employee manages two departments. As useful as this might be, this request might be a better way to determine departmental managers by also including the department number they manage:

1 Row ReturnedINTERSECT

Now, there is only one row returned. Since there are two columns from each table, both columns are used in the join. Therefore, employee 1256349 works in department 400, so both matched and it was returned. This person also manages department 100, but since 400 is not equal to 100, that row is not returned. There are limitations that need to be considered when using INTERSECT.

As this illustrates, the INTERSECT is a bit limited in its flexibility to return columns that are outside the domain of both tables. Therefore, one of the best techniques for using the INTERSECT is for populating a derived table or in a view.

Once the table has been derived, in parallel using the SET operator, it can then be used to perform either an INNER or OUTER join to obtain the additional columns from one or more other tables.

The next INTERSECT populates a derived table and then joins it to the Employee table to find out the students taking Introduction to SQL:


4 Rows Returnednext INTERSECT populates a derived table

This is not a great example since the INNER JOIN could have been performed on the two tables to get the same result. However, it does demonstrate the mechanics of using a SET operator in a derived table. This example will be used again with the EXCEPT because it does provide an ability not easily achieved with other techniques.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics