|=--------------------------------------------------=|
|03.10 - Set operations .
|=--------------------------------------------------=|
To study the set operations first we will create
new table as exact duplicate of the users table. We
will simply call it users2.
create table users2 as select * from users;
First we will remove 2 rows for the users2 table.
This will result in more illustrative examples.
delete from users2
where user_key in (3,4);
To use the set operators all used tables must have
corresponding column data types.
First we will use UNION ALL. This set operation
will select all rows from first table. Then it will
select all rows from second table (or third, fourth).
The result of this query will then concatenate all
the rows together to one result. For us to better
distinct between the result of both tables we will
add name of the source table as new string column
named tab.
select 'users' tab, u.* from users u
union all
select 'users2' tab, u2.* from users2 u2;
The last set operator we will examine is INTERSECT.
And this operator queries first table and second
table and find the rows that's in both of the tables.
The query:
select * from users
intersect
select * from users2;
Will result in the data set of rows that are
contained in both of the tables.