Differentiate between intersect () and union ().

Let take for example we have two tables manager and customer.  Both of these tables have somewhat the same structure such as the following columns:

  1. FirstName
  2. LastName
  3. AddressLine1
  4. City
  5. StateProvinceCode
  6. PostalCode

Create Manager table

CREATE TABLE [dbo].[manager](

[ID] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [nvarchar](100) NOT NULL,

[LastName] [nvarchar](100) NOT NULL,

[AddressLine1] [nvarchar](100) NOT NULL,

[City] [nvarchar](100) NULL,

[StateProvinceCode] [nvarchar](100) NULL,

[PostalCode] [int] NOT NULL,

 CONSTRAINT [PK_manager] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Create Customer table

CREATE TABLE [dbo].[customer](

[ID] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [nvarchar](100) NOT NULL,

[LastName] [nvarchar](100) NOT NULL,

[AddressLine1] [nvarchar](100) NOT NULL,

[City] [nvarchar](100) NULL,

[StateProvinceCode] [nvarchar](100) NULL,

[PostalCode] [int] NOT NULL,

 CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

We want to do two queries:

  • Find the occurrences where a manager is a customer (intersect)
  • Find the occurrences where the manager is not a customer (except)

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   M.FirstName, 

         M.LastName, 

         M.AddressLine1, 

         M.City, 

         M.StateProvinceCode, 

         M.PostalCode 

FROM     manager M 

inner join customer 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   M.FirstName, 

         M.LastName, 

         M.AddressLine1, 

         M.City, 

         M.StateProvinceCode, 

         M.PostalCode 

FROM     manager M 

WHERE    NOT EXISTS (SELECT * FROM customer 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

Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*