Oracle union, intersect and minus - Dec 03, 2008 at 15:00 PM
by Rajmeet Ghai
Explain union, intersect and minus with examples.
UNION: The UNION operator is used to combine the result-set of
two or more SELECT statements Tables of both the select statement must have the
same number of columns with similar data types. It eliminates duplicates.
Syntax:
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Example:
SELECT emp_Name FROM Employees_india
UNION
SELECT emp_Name FROM Employees_USA
INTERSECT allows combining results of two or more select
queries. If a record exists in one query and not in the other, it will be
omitted from the INTERSECT results.
Syntax:
select field1, field2, . field_n from tables INTERSECT select field1,
field2, . field_n from tables;
Example:
select salary from employee INTERSECT select salary from manager;
SQL MINUS returns all rows in the first query that are not
returned in the second query. Each statement must have the same number of
fields in the result sets with similar data types.
Syntax:
Select field1, field2, . field_n from tables
MINUS
select field1, field2, . field_n from tables;
Example:
Select salary from employee
MINUS
select salary from manager
|