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
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:
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 Returned
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:
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.