Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 12, 2004

MDX Essentials: Basic Set Functions: The EXCEPT() Function - Page 2

By William Pearson

The EXCEPT() Function

The EXCEPT() function, according to the Analysis Services Books Online, "finds the difference between two sets, optionally retaining duplicates." We will examine the use of EXCEPT() to manage this, both with and without duplicates, in the sections that follow.

In keeping with the approach we have used in other lessons within the MDX Essentials series, we will examine the syntax for the EXCEPT() function in general, building to a determination of the difference between two sets after creating and running an individual query for each; this will show that the EXCEPT() function does, in fact, generate the results we might expect. Next, we will explore an example that introduces duplication within an EXCEPT() function, and the means that MDX affords us for managing duplication. Consistent with the design of our series, our objective is to gain a richer understanding of the options we have within the EXCEPT() function, as well as an overall perspective of how the function can be used to meet our business needs.


The EXCEPT() function allows us to return a set consisting of the difference between a pair of sets upon which it is enacted. The syntax with which we employ EXCEPT() allows us an optional flag, as was the case with the INTERSECT() and UNION() functions, among others; the flag allows us the capability of presenting duplicates that might occur within the sets we are subjecting to the EXCEPT() function. The default for the function (without the flag), again like the INTERSECT() and UNION() functions, is the elimination of duplicates, which would likely be the typical requirement.

Let's look at some syntax illustrations to further clarify the operation of EXCEPT().


Syntactically, the sets between which a difference is to be determined by the EXCEPT() function are placed within the parentheses to the right of EXCEPT, and separated by a comma. The optional ALL flag overrides the default behavior of the function, and allows duplicates to remain prior to the determination of the difference. The syntax is shown in the following string:

EXCEPT(Set1, Set2[, ALL])

The EXCEPT() function returns, in effect, whatever appears in the first set, and that does not appear in the second set, upon which the function is being enacted. This might also be expressed as the "data that is not in common between the two sets." Importantly, the two sets must be composed of the same dimension, and must exist at the same level within the dimension, for the EXCEPT() function to work.

While the default behavior of the function dictates that duplicates are eliminated prior to the determination of the difference, use of the optional ALL flag allows duplicate members to remain within the newly produced set, as we shall see in a step in the practice example. Duplicates matching in the first set are discarded, while non-matching duplicates are retained.

The following query contains an example EXCEPT() function. We are selecting the total annual General and Administrative ("G & A") expense for 1998 for the set of all store countries, minus the subset of the Canadian store countries.


{Measures.[Amount] } ON COLUMNS,

   { EXCEPT ([Store].[Store Country].Members, {[Store Country].[Canada]} )} 
      ON ROWS




   ([Account].[All Account].[Net Income].[Total Expense].[General & Administration],


This simple query would result in the return of a dataset similar to that depicted in Table 1.





$ 32,513.40

Table 1: Results of a Simple Query Containing the EXCEPT() Function, with Total G & A Expense for 1998 as the Measure

We will practice the use of the EXCEPT() function in the section that follows. Moreover, we will explore the use of the ALL flag we have discussed, to activate and reinforce the concept of its use in retaining duplicates.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM