Operations on Result Sets in SQL
Operations on Result Sets in SQL
Sometimes it is useful to combine query results from two or more queries into a single result.
SQL supports three set operators which have the pattern:
<query 1> <set operator> <query 2>
The set operators are:
• union [all] returns a table consisting of all rows either appearing in the result of <query
1> or in the result of <query 2>. Duplicates are automatically eliminated unless the
clause all is used.
• intersect returns all rows that appear in both results <query 1> and <query 2>.
• minus returns those rows that appear in the result of <query 1> but not in the result of
<query 2>.
Example: Assume that we have a table EMP2 that has the same structure and columns
as the table EMP:
• All employee numbers and names from both tables:
select EMPNO, ENAME from EMP
union
select EMPNO, ENAME from EMP2;
• Employees who are listed in both EMP and EMP2:
select from EMP
intersect
select from EMP2;
• Employees who are only listed in EMP:
select from EMP
minus
select from EMP2;
Each operator requires that both tables have the same data types for the columns to which the operator is applied.
SQL supports three set operators which have the pattern:
<query 1> <set operator> <query 2>
The set operators are:
• union [all] returns a table consisting of all rows either appearing in the result of <query
1> or in the result of <query 2>. Duplicates are automatically eliminated unless the
clause all is used.
• intersect returns all rows that appear in both results <query 1> and <query 2>.
• minus returns those rows that appear in the result of <query 1> but not in the result of
<query 2>.
Example: Assume that we have a table EMP2 that has the same structure and columns
as the table EMP:
• All employee numbers and names from both tables:
select EMPNO, ENAME from EMP
union
select EMPNO, ENAME from EMP2;
• Employees who are listed in both EMP and EMP2:
select from EMP
intersect
select from EMP2;
• Employees who are only listed in EMP:
select from EMP
minus
select from EMP2;
Each operator requires that both tables have the same data types for the columns to which the operator is applied.
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal