T-SQL queries with examples of Join in SQL Server

create table month1

(

       supplierid int,

       customerid int

)

create table month2

(

       supplierid int,

       customerid int

)

INSERT INTO month1 VALUES (1, 100)

INSERT INTO month1 VALUES (2, 200)

INSERT INTO month1 VALUES (3, 300)

INSERT INTO month1 VALUES (2, 400)

INSERT INTO month1 VALUES (4, 500)

INSERT INTO month1 VALUES (2, 600)

INSERT INTO month1 VALUES (5, 800)

INSERT INTO month2 VALUES (1, 700)

INSERT INTO month2 VALUES (2, 200)

INSERT INTO month2 VALUES (2, 400)

INSERT INTO month2 VALUES (3, 600)

INSERT INTO month2 VALUES (3, 300)

INSERT INTO month2 VALUES (4, 500)

SELECT * FROM month1

SELECT * FROM month2

--New customers in month2 along with Suppliers

SELECT m2.*

FROM month2 m2

LEFT OUTER JOIN month1 m1

       ON m2.customerid = m1.customerid

WHERE m1.customerid IS NULL

--Suppliers that have retained their customers in month2

SELECT m1.*

FROM month1 m1

INNER JOIN month2 m2

       ON m1.customerid = m2.customerid

             AND m1.supplierid = m2.supplierid

--All the customers that have changed their supplier

SELECT

       m1.*

   ,m2.supplierid

FROM month1 m1

INNER JOIN month2 m2

       ON m1.customerid = m2.customerid

             AND m1.supplierid <> m2.supplierid

--All the customers that were in month1 but are not in month2

SELECT

       m1.*

FROM month1 m1

LEFT OUTER JOIN month2 m2

       ON m1.customerid = m2.customerid

WHERE m2.customerid IS NULL

--All the suppliers that have lost their customers

SELECT

       m1.*

FROM month1 m1

INNER JOIN month2 m2

       ON m1.customerid = m2.customerid

             AND m1.supplierid <> m2.supplierid

UNION

SELECT

       m1.*

FROM month1 m1

LEFT OUTER JOIN month2 m2

       ON m1.supplierid = m2.supplierid

WHERE m2.supplierid IS NULL