UNION ,UNION ALL ,INTERSECT and EXCEPT

UNION ,UNION ALL ,INTERSECT and EXCEPT

ex)  CREATE TABLE M1
      (
       NUM INT IDENTITY(1,1) NOT NULL,
      NAME VARCHAR(1) NOT NULL
       );

SELECT * INTO M2 FROM M1;

 

SELECT * FROM M1;
 INSERT INTO M1(NAME) VALUES('A');
 INSERT INTO M1(NAME) VALUES('B');
 INSERT INTO M1(NAME) VALUES('C');
 INSERT INTO M1(NAME) VALUES('H');

 

SELECT * FROM M2;
 INSERT INTO M2(NAME) VALUES('D');
 INSERT INTO M2(NAME) VALUES('E');
 INSERT INTO M2(NAME) VALUES('F');
 INSERT INTO M2(NAME) VALUES('H');

 

SELECT * FROM M1                SELECT * FROM M1
union      -합집합                     union all  -- 합집합 and 교집합 
SELECT * FROM M2                SELECT * FROM M2

               

 

SELECT * FROM M1                         SELECT * FROM M1
 intersect           -- 교집합                 EXCEPT                 -- M1- 교집합
SELECT * FROM M2;                        SELECT * FROM M2;