This article is written about how to use the Oracle UNION operator with syntax and examples.
The Oracle UNION operator is used to mix the end result units of 2 or greater Oracle SELECT statements. It eliminates reproduction rows between the a number SELECT statements.
Each SELECT declaration inside the UNION operator ought to have the equal wide variety of fields in the end result sets with similar statistics types.
The syntax for the UNION operator in Oracle/PLSQL is:
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
Parameters or Arguments
expression1, expression2, expression_n The columns or calculations that you want to retrieve. tables The tables that you want to retrieve documents from. There ought to be at least one table listed in the FROM clause. WHERE conditions Optional. The prerequisites that must be met for the archives to be selected.
There need to be identical range of expressions in each SELECT statements.
Example – Returns single field
The following is an example of the Oracle UNION operator that returns one subject from multiple SELECT statements (and each fields have the identical facts type):
SELECT supplier_id FROM suppliers UNION SELECT supplier_id FROM order_details;
In this Oracle UNION operator example, if a supplier_id appeared in each the suppliers and order_details table, it would appear once in your result set. The Oracle UNION operator gets rid of duplicates. If you do not wish to remove duplicates, try the usage of the Oracle UNION ALL operator.
Example – Using ORDER BY
The Oracle UNION operator can use the ORDER BY clause to order the consequences of the query.
SELECT supplier_id, supplier_name FROM suppliers WHERE supplier_id <= 500 UNION SELECT company_id, company_name FROM companies WHERE company_name = 'Apple' ORDER BY 2;
In this Oracle UNION operator, due to the fact that the column names are specific between the two SELECT statements, it is more fantastic to reference the columns in the ORDER BY clause with the aid of their function in the end result set. In this example, we have sorted the effects via supplier_name / company_name in ascending order, as denoted by means of the ORDER BY 2
The supplier_name / company_name fields are in role #2 in the result set.
Frequently Asked Questions
Question: I need to compare two dates and return the depend of a area based totally on the date values. For example, I have a date subject in a table referred to as remaining updated date. I have to test if trunc(last_updated_date >= trunc(sysdate – 13).
Answer: Since you are the usage of the Oracle COUNT feature which is an mixture function, we would suggest using the Oracle UNION operator. For example, you should try the following:
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode) FROM cdmaster a, nmmaster b WHERE a.code = b.code AND a.status = 1 AND b.status = 1 AND b.Ncode <> 'a10' AND TRUNC(a.last_updated_date) <= TRUNC(sysdate - 13) GROUP BY a.code, a.name UNION SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode) FROM cdmaster a, nmmaster b WHERE a.code = b.code AND a.status = 1 AND b.status = 1 AND b.Ncode <> 'a10' AND TRUNC(a.last_updated_date) > TRUNC(sysdate - 13) GROUP BY a.code, a.name;
The Oracle UNION operator approves you to function a matter based on one set of criteria.
TRUNC(a.last_updated_date) <= TRUNC(sysdate - 13)
As nicely as function a matter primarily based on some other set of criteria.
TRUNC(a.last_updated_date) > TRUNC(sysdate - 13)