SQL_Joins_Intersect_Except_Union, Sql Server

SQL_Joins_Intersect_Except_Union

--Sql Server 2014 Express Edition
--Batches are separated by 'go'
-- SQL_Joins_Intersect_Except_Union
select @@version as 'sql server version'
GO
CREATE VIEW Lunch 
AS 
  SELECT 'Beer' AS item 
  UNION SELECT 'Olives' 
  UNION SELECT 'Bread' 
  UNION SELECT 'Salami' 
  UNION SELECT 'Calamari' 
  UNION SELECT 'Coffee'
  UNION SELECT NULL
GO

CREATE VIEW Dinner 
AS 
  SELECT 'Wine' AS item 
  UNION SELECT 'Olives' 
  UNION SELECT 'Bread' 
  UNION SELECT 'Steak' 
  UNION SELECT 'Aubergines' 
  UNION SELECT 'Salad' 
  UNION SELECT 'Coffee' 
  UNION SELECT 'Apple'
  UNION SELECT NULL
GO
select 'UNION LUNCH and DINNER' [UNION LUNCH-DINNER]
-- union to get everything distinct items you have eaten today thru lunch and dinner
SELECT item FROM Lunch
UNION 
SELECT item FROM Dinner;

-- same above can be achieved by full outer join with coalesce()
SELECT DISTINCT COALESCE(Lunch.item, Dinner.item) AS item
FROM Lunch 
  FULL OUTER JOIN Dinner 
  ON Dinner.item = Lunch.item 
  
-- now get items from lunch which you have not eaten in dinner  and you can get do vice-versa
SELECT item FROM Lunch
EXCEPT 
SELECT item FROM Dinner;

-- same above can be achieved by using left outer join where dinner-items are null and can be done for vice versa
SELECT Lunch.item 
FROM Lunch 
  LEFT OUTER JOIN Dinner 
  ON Dinner.item = Lunch.item 
WHERE dinner.item IS NULL;

-- now lets get what items were eaten in lunch and dinner both
select 'Intersect LUNCH and DINNER' [Intersect LUNCH-DINNER]
SELECT item FROM Dinner
INTERSECT 
SELECT item FROM Lunch;
select 'InnerJoin LUNCH and DINNER' [InnerJoin LUNCH-DINNER]
-- same above can be achieved by inner join
SELECT Dinner.item 
FROM Dinner 
  INNER JOIN Lunch
  ON Dinner.item = Lunch.item;
--   --Items you only ate once in the day.
 SELECT item 
FROM 
  (
    SELECT item FROM Lunch
    EXCEPT SELECT item FROM Dinner
  ) Only_Lunch
UNION 
SELECT item 
FROM 
  (
    SELECT item FROM Dinner
    EXCEPT SELECT item FROM Lunch
  ) Only_Dinner;


run \| edit \| history \| help	0