Show a SQL Server query result in set where both sets of data match or only where data exists in one of the tables and not the other using INTERSECT and EXCEPT. INTERSECT gives you the final result set where values in both of the tables match. EXCEPT gives you the final result set where data exists in the first dataset and not in the second dataset.
-- -----------------------------------------------------------------------------
-- INTERSECT
-- -----------------------------------------------------------------------------
--
-- If we want to find out which people exist in both the customer table and the
-- manager table and get a distinct list back we can issue the following command
-- -----------------------------------------------------------------------------
select FIRSTNAME,
LASTNAME,
ADDRESSLINE1,
CITY,
STATEPROVINCECODE,
POSTALCODE
from MANAGER
intersect
select FIRSTNAME,
LASTNAME,
ADDRESSLINE1,
CITY,
STATEPROVINCECODE,
POSTALCODE
from CUSTOMER;
--
-- To do this same thing with a regular T-SQL command:
select M.FIRSTNAME,
M.LASTNAME,
M.ADDRESSLINE1,
M.CITY,
M.STATEPROVINCECODE,
M.POSTALCODE
from MANAGER as M
inner join CUSTOMER as C
on M.FIRSTNAME = C.FIRSTNAME
and M.LASTNAME = C.LASTNAME
and M.ADDRESSLINE1 = C.ADDRESSLINE1
and M.CITY = C.CITY
and M.POSTALCODE = C.POSTALCODE
group by M.FIRSTNAME,
M.LASTNAME,
M.ADDRESSLINE1,
M.CITY,
M.STATEPROVINCECODE,
M.POSTALCODE;
-- -----------------------------------------------------------------------------
-- EXCEPT
-- -----------------------------------------------------------------------------
-- If we want to find out which people exists in the manager table, but not in
-- the customer table and get a distinct list back we can issue the following
-- command
-- -----------------------------------------------------------------------------
select FIRSTNAME,
LASTNAME,
ADDRESSLINE1,
CITY,
STATEPROVINCECODE,
POSTALCODE
from MANAGER
except
select FIRSTNAME,
LASTNAME,
ADDRESSLINE1,
CITY,
STATEPROVINCECODE,
POSTALCODE
from CUSTOMER;
--
-- To do this same thing with a regular T-SQL command:
select M.FIRSTNAME,
M.LASTNAME,
M.ADDRESSLINE1,
M.CITY,
M.STATEPROVINCECODE,
M.POSTALCODE
from MANAGER as M
where not exists (
select * from CUSTOMER as C
where M.FIRSTNAME = C.FIRSTNAME
and M.LASTNAME = C.LASTNAME
and M.ADDRESSLINE1 = C.ADDRESSLINE1
and M.CITY = C.CITY
and M.POSTALCODE = C.POSTALCODE
)
group by M.FIRSTNAME,
M.LASTNAME,
M.ADDRESSLINE1,
M.CITY,
M.STATEPROVINCECODE,
M.POSTALCODE;