GeekPhilosopher: Except

Description:	Works like a left outer join. Returns all rows on the left table except the right table matching rows. Used to identify rows that are different between two tables with the same column structure.
Syntax:	{ <query_specification> \| ( <query_expression> ) } { EXCEPT} { <query_specification> \| ( <query_expression> ) }a
Arguments:	<query_specification> \| ( <query_expression> ): query specification or query expression that returns data to be compared with the data from another query specification or query expression
Sql Server Version:	2005+.

Note: This sample uses the AdventureWorks database which can be downloaded here

1.	Create the tables that will be used to test the "Except" statement. They will contain identical and unique rows.

--The two tables use a different column in the "order by" designation

--which will cause the tables to be slightly different

--These results have been ordered by Sales.Currency.CurrencyCode

select currencycode, name

into dbo.subsetCurrecyCodeA

from

(

select *, ROW_NUMBER() over (order by curr.currencycode) rowNum

from Sales.Currency as curr

) queryA

where rowNum between 1 and 30

--These results have been ordered by Sales.Currency.Name

select currencycode, name

into dbo.subsetCurrecyCodeB

from

(

select *, ROW_NUMBER() over (order by curr.name) rowNum

from Sales.Currency as curr

) queryB

where rowNum between 1 and 30

2.	The following tables were created in step-1.

Table: subsetCurrecyCodeA		Table: subsetCurrecyCodeB
currencycode	name	currencycode	name
AED	Emirati Dirham	AFA	Afghani
AFA	Afghani	AMD	Armenian Dram
ALL	Lek	ARS	Argentine Peso
AMD	Armenian Dram	AUD	Australian Dollar
ANG	Netherlands Antillian Guilder	AWG	Aruban Guilder
AOA	Kwanza	AZM	Azerbaijanian Manat
ARS	Argentine Peso	BBD	Barbados Dollar
ATS	Shilling	BEF	Belgian Franc
AUD	Australian Dollar	BGN	Bulgarian Lev
AWG	Aruban Guilder	BHD	Bahraini Dinar
AZM	Azerbaijanian Manat	BND	Brunei Dollar
BBD	Barbados Dollar	BOB	Boliviano
BDT	Taka	BRL	Brazilian Real
BEF	Belgian Franc	BSD	Bahamian Dollar
BGN	Bulgarian Lev	CAD	Canadian Dollar
BHD	Bahraini Dinar	CLP	Chilean Peso
BND	Brunei Dollar	COP	Colombian Peso
BOB	Boliviano	CRC	Costa Rican Colon
BRL	Brazilian Real	CYP	Cyprus Pound
BSD	Bahamian Dollar	CZK	Czech Koruna
BTN	Ngultrum	DEM	Deutsche Mark
CAD	Canadian Dollar	DKK	Danish Krone
CHF	Swiss Franc	DOP	Dominican Peso
CLP	Chilean Peso	DZD	Algerian Dinar
CNY	Yuan Renminbi	GHC	Cedi
COP	Colombian Peso	HRK	Croatian Kuna
CRC	Costa Rican Colon	PAB	Balboa
CYP	Cyprus Pound	THB	Baht
CZK	Czech Koruna	VEB	Bolivar
DEM	Deutsche Mark	XOF	CFA Franc BCEAO

3.	The following query will return the rows that exist only on subsetCurrecyCodeA. In other words, it returns all rows except for the rows that exist in subsetCurrecyCodeB.

select * from subsetCurrecyCodeA

except

select * from subsetCurrecyCodeB

currencycode	name
AED	Emirati Dirham
ALL	Lek
ANG	Netherlands Antillian Guilder
AOA	Kwanza
ATS	Shilling
BDT	Taka
BTN	Ngultrum
CHF	Swiss Franc
CNY	Yuan Renminbi

4.	The following query will return the rows that exist only on subsetCurrecyCodeB. In other words, it returns all rows except for the rows that exist in subsetCurrecyCodeA.

select * from subsetCurrecyCodeB
except
select * from subsetCurrecyCodeA