EXCEPT Teradata

Compatibility: ANSI

The EXCEPT set operator is used to eliminate common domain values from the answer set by throwing away the matching values. This is the primary SET operator that provides a capability not available using either an INNER or OUTER JOIN. Although the OUTER JOIN does return missing row data, it also includes the matching or intersecting rows. The EXCEPT will only return the missing domain rows and therefore provides a unique capability.

The syntax of the EXCEPT:

The following example will return all departments without employees, therefore, the Department table is in the first SELECT:

1 Row Returned

Department_Number

500

As mentioned earlier in this chapter, the restriction regarding the use of only domain columns, makes the output a bit cryptic and it might be nice to see the department name.

The next SELECT uses a derived table to obtain the department for a join with the department data to return the name of the department without employees working in it:


1 Row Returned

Departments_without_Employees

Human Resources


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

Teradata Topics