EXCEPT or MINUS Set operator in SQL

MINUS (EXCEPT) Operator

Let us use the following tables for explaining the concept of the set operators;

Regno	Name	Phone
10BS0123	Madhavan	9965235412
10BC0234	Jerry	8569326541
11BM0023	Malar	9658236542
11BC0003	Kumar	9653268954

 Table 2 - S_Cricket (Students registered for Cricket)

Regno	Name	Phone
10MS0434	Noel	9856452314
10MS0012	Kumar	9653268954
11MC0070	Ramkumar	9965235412
11BC0003	Kumar	9653268954

 Table 2 - S_Basketball (Students registered for Basketball)

MINUS is used to filter records that are unique to first expression only. That is, the system will find all the common records of expression 1 and 2, remove the common records, and display only records from the result of expression 1. For example, consider the query given below, which gives the students details who have registered only for cricket.

(SELECT Name, Phone FROM S_Cricket) MINUS (SELECT Name, Phone FROM S_Basketball);

----- Query 1

The result of this query will be;

Regno	Name	Phone
10BS0123	Madhavan	9965235412
10BC0234	Jerry	8569326541
11BM0023	Malar	9658236542

 
 Table 3 shows the result generated from Query 1. Here, except the common record, i.e, except ('11BC0003', 'Kumar', 9653268954), the result will contain all the other records of result of expression 1.

Support for SQL Set Operator MINUS (EXCEPT) in Various DBMSs:

MINUS (EXCEPT) operator in MySQL

MySQL does not support MINUS (EXCEPT). It can be achieved through other ways using the keywords NOT IN or NOT EXISTS. Query 1 is rewritten using the keyword NOT IN as follows to achieve exception in MySQL.

SELECT Name, Phone FROM S_Cricket WHERE (Name, Phone) NOT IN (SELECT Name, Phone FROM S_Basketball);

----- Query 2

 

MINUS (EXCEPT) operator in Oracle

Oracle supports MINUS operator as discussed above. The above example (Query 1) can be treated as example for Oracle MINUS operator.

MINUS (EXCEPT) operator in DB2

DB2 supports EXCEPT operator. Above examples can be treated as examples for DB2 with MINUS replaced as EXCEPT. An example is shown in Query 3;

(SELECT Name, Phone FROM S_Cricket) EXCEPT (SELECT Name, Phone FROM S_Basketball);

----- Query 3

Related Articles

SQL Set Operators
UNION Operator
INTERSECT Operator

ALTER TABLE statement in SQL

ALTER TABLE statement in SQL 

ALTER TABLE statement

ALTER TABLE is a Data Definition Language (DDL) statement that alters the structure definition of an existing database table. This statement can be used to alter the structure along with the following commands;

Command	Purpose	Conditions must hold
ADD	To add new attributes (columns) with the existing table	You can either add a NOT NULL attribute with the empty relation (table) or add only NULL attributes if the table is have some records in it.
MODIFY	To modify the data type and size of existing attributes	While you modify, the new data type and size should not be less than the old type and size if the table has data. you can modify table as per your wish only if it is empty (without records).
DROP COLUMN	To remove existing attribute	Attribute can be removed only if it is not being referred by another attribute/another table
DROP CONSTRAINT	To remove existing integrity constraints (CHECK, FOREIGN KEY, UNIQUE, PRIMARY KEY)	We need to check whether the attribute is referred by other attributes/other table.
ADD CONSTRAINT	To add new constraints on existing attributes	New constraints can be added if the table is empty. Else, adding constraints is a complex task.

Syntax:

ALTER TABLE table_name

[ADD (column_name1 datatype(size), column_name2 datatype(size), …]

[MODIFY (column_name new_datatype(size)]

[DROP COLUMN existing_column_name]

[DROP CONSTRAINT existing_constraint_name]

[ADD CONSTRAINT constraint_name constraint constraint_definition];

Examples:

Let us consider the Furnitures table that was created using the following DDL statement for our example;

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Furniture_Type VARCHAR(20) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

CONSTRAINT pk PRIMARY KEY (Furniture_ID),

CONSTRAINT ch1 CHECK (Quantity > 10),

CONSTRAINT ch2 CHECK (Furniture_Type in (‘Chair’, ‘Table’, ‘Cot’))

);

1) ADD

The following statement will add a new attribute PRICE with furnitures table;

ALTER TABLE furnitures ADD (Price NUMBER(7,2));

Before ALTER	After ALTER
Attributes Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Design_No	Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Design_No Price

2) MODIFY

The following statement will modify the manufacturer attribute with new size;

ALTER TABLE furnitures MODIFY (Manufacturer VARCHAR(35) NOT NULL);

Before ALTER	After ALTER
Manufacturer VARCHAR(25) NOT NULL	Manufacturer VARCHAR(35) NOT NULL

3) DROP COLUMN

The following statement will remove Design_No column from furnitures table;

ALTER TABLE furnitures DROP COLUMN Design_No;

Before ALTER	After ALTER
Attributes Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Design_No Price	Furniture_ID Furniture_Name Furniture_Type Manufacturer Quantity Price

4) DROP CONSTRAINT

The following statement will remove the CHECK constraint with the name ch1; [Note: it will not remove the column on which check constraint applied]

ALTER TABLE furnitures DROP CONSTRAINT ch1;

Before ALTER	After ALTER
Quantity must be a value greater than 10	Quantity can be any number of size not more than 5 Constraint ch1 removed

5) ADD CONSTRAINT

The following statement will add a new constraint on newly added attribute Price;

ALTER TABLE furnitures ADD CONSTRAINT ch3 CHECK (Price BETWEEN 1500 AND 20000);

Before ALTER	After ALTER
No constraint on Price	Price must be in the range 1500 and 20000.

***********

Go to CREATE TABLE page

Go to SQL home page

alter table statement in sql oracle, 

how add, modify, drop attributes in a database table, 

how to add a new integrity constraint with the existing table, 

how to remove an integrity constraint from a database table definition  

when can we modify a database table using ALTER TABLE statment

the conditions to be checked before modifying a database table definition

important considerations before redefining a table structure in SQL