Use EXCEPT keyword to eliminate duplicate data
The EXCEPT keyword will compare two different datasets and show only the records that are found in the first query and are not found in the second query. In the following example only records that are found in table1 but not found in table2 will be shown.
select column1,column2,column3 from table1 except select column1,column2,column3 from table2
Example
In the database there is a Office_Computer table and a Office_Work_Order table, as shown below.
Table: Office_Computer table Computer Location Mfr Model Purchase -------- -------- --- ----- -------- jerry Lobby Dell Vostro 4310 2/16/2008 george Office 101 Dell Vostro 4700 6/15/2009 kramer Office 102 Dell OptiPlex 780 4/1/2009 elane Break Room HP Pavilion 235 12/12/1999 Table: Office_Work_Order table Work_Order Computer Category Opened Closed ---------- -------- -------- ------ ------ 100 elane Blue Screen of Death 5/14/2009 5/16/2009 101 george Install Software 9/3/2010 9/3/2010 102 elane Smoking CPU 10/18/2010 10/18/2010 103 kramer Install Software 10/22/2010 11/3/2010
My boss needs to know which computers never had a work order opened. The following EXCEPT query will display all the computers that are found in the Office_Computer table, but not found in the Office_Work_Order table (remember EXCEPT will show only the records that are found in the first query and are not found in the second query).
select Computer from Office_Computer except select Computer from Office_Work_Order RESULTS: Computer -------- jerry
