UNION INTERSECT
UNION Query
The UNION query allows you to combine the result sets of 2 or more "select" queries.
The UNION query allows you to combine the result sets of 2 or more "select" queries.
- It removes duplicate rows between the various "select" statements.
- Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION query is:
Select field1, field2, . field_n from tables
UNION
Select field1, field2, . field_n from tables;
Example #1
The following is an example of a UNION query:
Select supplier_id from suppliers
UNION
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.
Example #2 - With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
UNION ALL Query
The UNION ALL query allows you to combine the result sets of 2 or more "select" queries. It returns all rows (even if the row exists in more than one of the "select" statements).
Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.
UNION
Select field1, field2, . field_n from tables;
Example #1
The following is an example of a UNION query:
Select supplier_id from suppliers
UNION
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.
Example #2 - With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
UNION ALL Query
The UNION ALL query allows you to combine the result sets of 2 or more "select" queries. It returns all rows (even if the row exists in more than one of the "select" statements).
Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.
The syntax for a UNION ALL query is:
Select field1, field2, . field_n from tables
UNION ALL
Select field1, field2, . field_n from tables;
Example #1
The following is an example of a UNION ALL query:
Select supplier_id from suppliers
UNION ALL
Select supplier_id from orders;
If a supplier_id appeared in both the suppliers and orders table, it would appear multiple times in your result set. The UNION ALL does not remove duplicates.
Select field1, field2, . field_n from tables
UNION ALL
Select field1, field2, . field_n from tables;
Example #1
The following is an example of a UNION ALL query:
Select supplier_id from suppliers
UNION ALL
Select supplier_id from orders;
If a supplier_id appeared in both the suppliers and orders table, it would appear multiple times in your result set. The UNION ALL does not remove duplicates.
Example #2 - With ORDER BY Clause
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION ALL
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
The following is a UNION query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id > 2000
UNION ALL
Select company_id, company_name from companies where company_id > 1000
ORDER BY 2;
Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
INTERSECT Query
The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.
The syntax for an INTERSECT query is:
Select field1, field2, . field_n from tables
INTERSECT
Select field1, field2, . field_n from tables;
The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.
The syntax for an INTERSECT query is:
Select field1, field2, . field_n from tables
INTERSECT
Select field1, field2, . field_n from tables;
Example #1
The following is an example of an INTERSECT query:
Select supplier_id from suppliers
INTERSECT
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.
The following is an example of an INTERSECT query:
Select supplier_id from suppliers
INTERSECT
Select supplier_id from orders;
In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.
Example #2 - With ORDER BY Clause
The following is an INTERSECT query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id >2000
INTERSECT
Select company_id, company_name from companies where company_id >1000
ORDER BY 2;
The following is an INTERSECT query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id >2000
INTERSECT
Select company_id, company_name from companies where company_id >1000
ORDER BY 2;
Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
MINUS Query
The MINUS query returns all rows in the first query that are not returned in the second query.
Each SQL statement within the MINUS query must have the same number of fields in the result sets with similar data types.
The syntax for an MINUS query is:
Select field1, field2, . field_n from tables
MINUS
Select field1, field2, . field_n from tables;
The MINUS query returns all rows in the first query that are not returned in the second query.
Each SQL statement within the MINUS query must have the same number of fields in the result sets with similar data types.
The syntax for an MINUS query is:
Select field1, field2, . field_n from tables
MINUS
Select field1, field2, . field_n from tables;
Example #1
The following is an example of an MINUS query:
Select supplier_id from suppliers
MINUS
Select supplier_id from orders;
In this example, the SQL would return all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.
The following is an example of an MINUS query:
Select supplier_id from suppliers
MINUS
Select supplier_id from orders;
In this example, the SQL would return all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.
Example #2 - With ORDER BY Clause
The following is an MINUS query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id >2000
MINUS
Select company_id, company_name from companies where company_id >1000
ORDER BY 2;
The following is an MINUS query that uses an ORDER BY clause:
Select supplier_id, supplier_name from suppliers where supplier_id >2000
MINUS
Select company_id, company_name from companies where company_id >1000
ORDER BY 2;
Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
Performance issues:
Performance issue with UNION -Why?
Read this
A UNION is highly optimized and really fast, except in cases where one query finishes long before the other, and Oracle must wait to get the whole result set before starting sorting.
Use UNION ALL: The UNION ALL may be faster when you dont mind the possibility of having duplicate rows in the result set
Performance issues:
Performance issue with UNION -Why?
Read this
A UNION is highly optimized and really fast, except in cases where one query finishes long before the other, and Oracle must wait to get the whole result set before starting sorting.
Use UNION ALL: The UNION ALL may be faster when you dont mind the possibility of having duplicate rows in the result set
