SQL Union query for complex queries

Union is a joint query. It can select two or more data sets for various operations. Sometimes, we can filter duplicate data. Let's take a look at the instance.

Union is a joint query. It can select two or more data sets for various operations. Sometimes, we can filter duplicate data. Let's take a look at the instance.

UNION query allows you to combine two or more "select" query result sets. It eliminates repeated rows between various "select" reports.

Each SQL statement for internal query must have the same number of fields in the result set of similar data types.

The UNION query syntax is:

The Code is as follows:
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:

The Code is as follows:
Select supplier_id From suppliers UNION Select supplier_id From orders;

In this example, if supplier_id appears in the supplier and order table, it will appear in your result set once. The duplicate content is deleted.

Example #2-order by clause

The following is a UNION query using the order by clause.

The Code is as follows:
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;

Q: I need to compare one field count based on two dates and return date values. For example, the Date Field in a table is called the last update date. I want to check if TRUNC (last_updated_date> = TRUNC (SYSDATE-13 ).

A: Since you are using this aggregate function COUNT, we recommend that you use UNION query. For example, you can try the following methods:

The Code is as follows:

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 (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 (last_updated_date)> trunc (sysdate-13) Group by a. code, a. name;