Sql-server – T-SQL MINUS operator

exceptsql serversql server 2014

These are the tables I have created and inserted values in it:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')
INSERT INTO Customer (Customer_Name)
VALUES('Ornob')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')
INSERT INTO DVD (DVD_Name)
VALUES('Rurouni Kenshin')


INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (5,4)

In order to display customer names who did not purchase any DVD by using MINUS operator, this is what I have tried:

SELECT Customer_Name
FROM Customer
WHERE Customer_No 
IN
(SELECT Customer_No FROM Customer
 MINUS
 SELECT Customer_No FROM DVD_Purchase
)

And I'm getting the following error message:

Msg 156, Level 15, State 1, Line 123 Incorrect syntax near the keyword
'SELECT'. Msg 102, Level 15, State 1, Line 124 Incorrect syntax near
')'.

As shown in the error message, Incorrect syntax near ')' which I'm unable to figure out.

Other than that, can someone provide me the correct query?

Best Answer

MINUS is a name for the "set difference" operator that is used only by Oracle.

SQL Server uses the (standard) name, EXCEPT, for this operator. Replacing the MINUS with EXCEPT will solve the issue:

SELECT Customer_Name
FROM Customer
WHERE Customer_No 
  IN
  (SELECT Customer_No FROM Customer
   EXCEPT
   SELECT Customer_No FROM DVD_Purchase
  ) ;

By the way, since the external query and the subquery reference the same table+column (Customer . Customer_No) you could use NOT IN without EXCEPT. Assuming that the columns involved (Customer_No) are not nullable, this is equivalent:

SELECT Customer_Name
FROM Customer
WHERE Customer_No 
  NOT IN
  (SELECT Customer_No FROM DVD_Purchase
  ) ;
Related Question