SQL Set Operations and AUTO-INCREMENT

Set Operation in SQL

SQL supports few Set operations to be performed on table data. These are used to get meaningful results from data, under different special conditions.

Union

UNION is used to combine the results of two or more Select statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and datatype must be same in both the tables.

Example of UNION

The First table,

ID	Name
1	abhi
2	adam

The Second table,

ID	Name
2	adam
3	Chester

Union SQL query will be,

select * from First

UNION

select * from second 

The result table will look like,

ID	NAME
1	Abhi
2	Adam
3	Chester

Union All

This operation is similar to Union. But it also shows the duplicate rows.

Example of Union All

The First table,

ID	NAME
1	abhi
2	adam

The Second table,

ID	NAME
2	Adam
3	Chester

Union All query will be like,

select * from First

UNION ALL

select * from second 

The result table will look like,

ID	NAME
1	Abhi
2	Adam
2	Adam
3	Chester

Intersect

Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same. MySQL does not support INTERSECT operator.

Example of Intersect

The First table,

ID	NAME
1	Abhi
2	Adam

The Second table,

ID	NAME
2	Adam
3	Chester

Intersect query will be,

select * from First

INTERSECT

select * from second 

The result table will look like

ID	NAME
2	Adam

Minus

Minus operation combines result of two Select statements and return only those result which belongs to first set of result. MySQL does not support INTERSECT operator.

Example of Minus

The First table,

ID	NAME
1	Abhi
2	Adam

The Second table,

ID	NAME
2	Adam
3	Chester

Minus query will be,

select * from First

MINUS

select * from second 

The result table will look like,

ID	NAME
1	Abhi

In short,

UNION - the values of the first query are returned with the values of the second query eliminating duplicates.
MINUS - the values of the first query are returned with duplicates values of the second query removed from the first query.
UNION ALL - the values of both queries are returned including all duplicates
INTERSECT - only the duplicate values are returned from both queries.

SQL AUTO INCREMENT

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

---

Syntax for MySQL

The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "ID" column would be assigned a unique value. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".