select state from pubs..authors
intersect
select state from pubs..publishers


select ISNULL(state, '?') from pubs..authors
intersect
select ISNULL(state, '?') from pubs..publishers



select state from pubs..authors
except
select state from pubs..publishers


select state from pubs..publishers
except
select state from pubs..authors


select state from pubs..publishers
except
select state from pubs..authors



SELECT DISTINCT P.state FROM pubs..publishers P 
WHERE NOT EXISTS
(SELECT * from pubs..authors A WHERE A.state = P.state
OR (A.state IS NULL AND P.state IS NULL))


SELECT state
FROM (SELECT DISTINCT 'U' AS setname, state FROM pubs..publishers
      UNION ALL
	  
      SELECT DISTINCT NULL, 
      state FROM pubs..authors) 
      AS D1
GROUP BY state
HAVING COUNT(*) = 1 
AND MAX(setname) = 'U'



SELECT state
FROM (SELECT DISTINCT 'U' AS setname, state FROM pubs..publishers
      UNION ALL 
	  
      SELECT DISTINCT NULL, 
      state FROM pubs..authors) 
      AS D1
GROUP BY state
HAVING COUNT(*) = 1 
AND  MAX(setname) = 'U'



select state from pubs..publishers
except
select state from pubs..authors


DECLARE  @t TABLE(state CHAR(2))
INSERT INTO @t select  ISNULL(state, CAST('AA' as CHAR(2))) as state from pubs..publishers
select state from @t
where
state not in (select ISNULL(state, CAST('AA' as CHAR(2))) from pubs..authors)

select * from pubs..authors

--UPDATE pubs..authors set state = NULL where au_id = '172-32-1176'
--UPDATE pubs..authors set state = 'CA' where au_id = '172-32-1176'


SELECT state FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors



SELECT  state INTO #states FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors

DROP TABLE #states

select * from #states


SELECT state FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors


SELECT state FROM pubs..publishers P
EXCEPT
SELECT state FROM pubs..authors A
ORDER BY state





-- this will not work
SELECT pub_name, state FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors

-- this is not supported
SELECT pub_name from pubs..publishers
INTERSECT CORRESPONDING BY (state)
SELECT state FROM pubs..authors

SET ANSI_NULLS OFF

SELECT pub_name FROM (
SELECT pub_name, state FROM pubs..publishers
EXCEPT
SELECT P.pub_name, A.state FROM pubs..publishers AS P
JOIN pubs..authors A ON 1=1 
) PN



-- Plan compare
SELECT P.pub_name FROM pubs..publishers P
WHERE P.state IN
(
SELECT state FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors
)
OR
P.state IS NULL AND NOT EXISTS (SELECT * FROM authors WHERE state IS NULL)


SELECT P.pub_name FROM pubs..publishers P
WHERE ISNULL(P.state, '__') IN
(
SELECT ISNULL(state, '__') FROM pubs..publishers
EXCEPT
SELECT ISNULL(state, '__') FROM pubs..authors
)


SELECT P.pub_name FROM pubs..publishers P 
WHERE  NOT EXISTS
(
SELECT * from pubs..authors A 
WHERE A.state = P.state
OR
(A.state IS NULL AND P.state IS NULL)
)



-- will not work because comparison done on NULL
SELECT P.pub_name FROM pubs..publishers P
WHERE p.state in
(SELECT P.state from pubs..authors A WHERE A.state = P.state
OR (A.state IS NULL AND P.state IS NULL))


SELECT P.pub_name FROM pubs..publishers P
WHERE P.state IN
(NULL)



SELECT P.pub_name FROM pubs..publishers P
INTERSECT
(
SELECT state FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors
)



select * from pubs..publishers
select * from pubs..authors





SELECT pub_name FROM pubs..publishers
WHERE state in 
(SELECT state FROM pubs..publishers
EXCEPT
SELECT state from pubs..authors)

SELECT pub_name FROM pubs..publishers
WHERE state in 
(NULL)



SELECT au_fname, au_lname FROM pubs..authors
where state is null



SELECT DISTINCT state from pubs..publishers AS P
WHERE NOT EXISTS
(SELECT * FROM pubs..authors AS A
WHERE P.state = A.state
OR (A.state is null and P.state is NULL))


SET ANSI_NULLS ON


SET ANSI_NULLS OFF
SELECT pub_name from pubs..publishers AS P
where state IN 
(
SELECT state FROM pubs..publishers
EXCEPT
SELECT state FROM pubs..authors
)
SET ANSI_NULLS ON




select  DISTINCT state from pubs..publishers
where
state not in (select state from pubs..authors)
OR (state is null AND 0 = (SELECT count(*) from pubs..authors where state is null))

 
SELECT state FROM pubs..authors
EXCEPT
SELECT state FROM pubs..publishers


SELECT state FROM pubs..authors
INTERSECT
SELECT state FROM pubs..publishers





select distinct state from pubs..publishers


CREATE TABLE test1
(
data int
)

CREATE TABLE test2
(
data int
)


insert into test1 values (NULL)
insert into test2 values (NULL)


select data from test1 INTERSECT select data from test2

se


select state from pubs..publishers except select state from pubs..authors

select * from pubs..authors where state is null

select * from pubs..publishers where state is null


--DROP TABLE Products

CREATE TABLE Products
(
Name VARCHAR(25),
[Warehouse in State] CHAR(2)
)

--DROP TABLE Outlets
CREATE TABLE Outlets
(
Name VARCHAR(25),
[Operates in State] CHAR(2)
)

INSERT INTO Products VALUES ('Blue widget', 'CA')
INSERT INTO Products VALUES ('Green widget', 'UT')
INSERT INTO Products VALUES ('White widget', 'CT')
INSERT INTO Products VALUES ('Purple widget', NULL)
--select * from products

INSERT INTO Outlets VALUES ('Values Int', 'OR')
INSERT INTO Outlets VALUES ('5 & 10', 'CT')
INSERT INTO Outlets VALUES ('Jakes', 'UT')
INSERT INTO Outlets VALUES ('Royal Outlet', NULL)

select * from outlets

SELECT [Warehouse in State] from Products
EXCEPT 
SELECT [Operates in State] from Outlets

SE
