SQL LECTURE 10

Lecture 10

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

________________________________________

SQL UNION Example

Look at the following tables:

"Employees_Norway":

E_ID	E_Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

"Employees_USA":

E_ID	E_Name
01	Turner, Sally
02	Kent, Clark
03	Svendson, Stephen
04	Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway

UNION

SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will be listed. The UNION command selects only distinct values.

________________________________________

SQL UNION ALL Example

Now we want to list all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway

UNION ALL

SELECT E_Name FROM Employees_USA

Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

The SQL SELECT INTO statement can be used to create backup copies of tables.

________________________________________

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table.

The SELECT INTO statement is most often used to create backup copies of tables.

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s)

INTO new_table_name [IN externaldatabase]

FROM old_tablename

________________________________________

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.

We use the following SQL statement:

SELECT *

INTO Persons_Backup

FROM Persons

We can also use the IN clause to copy the table into another database:

SELECT *

INTO Persons_Backup IN 'Backup.mdb'

FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName

INTO Persons_Backup

FROM Persons

________________________________________

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname

INTO Persons_Backup

FROM Persons

WHERE City='Sandnes'

________________________________________

SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.

The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo

INTO Persons_Order_Backup

FROM Persons

INNER JOIN Orders

ON Persons.P_Id=Orders.P_Id