Unions

The UNION statement allows you to combine multiple queries together to create a single result set.  All queries in the union SELECT statements must have an equal number of expressions.  In addition, these expressions (column names, literals, dates, results from functions, etc.) must be of compatible data types.

This example (using the sample database) will create a union using 3 queries to obtain information from the Employees table.  The first query will obtain employees in the Sales Department.  The second query will obtain employees in the Marketing Department, and the third query will obtain employees in the Training Department.   The Union will combine the results of the three queries.

These are the union types available in Argos:

Icon	Description
	A standard union combines the results of the two queries into one result set excluding any duplicate records.
	A UNION ALL union combines the results of the two queries into one result set and does not remove any duplicate records.
	An INTERSECT union returns only those records that exist in both queries.
	A MINUS union returns all records from the first query except for those records which also exist in the second query.

Creating the Main Query

Launch the Argos DataBlock Designer, add a multi-column list box to the Design Area, double-click on the object to launch the Build Query dialog box.  Create the query shown below to select last name, first name, and department name for employees in the Sales Department.  This query will be used as the basis for the creation of the other two queries.

The SELECT clause:

The WHERE clause:

After creating the query, you can click OK to test the query, or merely move on to the next step to create the Unions.

Creating the Unions

Click the “Show Unions” button at the top of the Build Query dialog box, and the Unions pane will appear as shown in the figure below. 

The query created in the previous step is shown as “Main Query”.   Since the Main Query will be used as the basis for the other queries, click the Copy icon (Under the Unions pane) to place the query into the clipboard.Do not click the Copy on the top of the Build Query dialog box; you must use the Copy icon within the Unions pane.

Click the Union All icon which creates “Union All” under the Main Query.  Right-click on “Union all” to edit the Union.  Change the Union Name to “Marketing”.  Note that this example uses Union All, however you should use the type of Union that applies to your needs.

Click Paste (under Unions Pane) to bring up the previous query, which now appears within the Build Query dialog box.   In the WHERE tab, change the Condition to “Marketing”.  The Union SQL statement for the Marketing Department has now been created.

Click the Union All icon again to create the third query and name it “Training”.

Click Paste again and change the Condition to ‘training’ in the WHERE tab of the Build Query dialog box.  This creates the Union SQL statement for the Training Department.

The screen should now look as shown in the figure below. Note the existence of the main query and the two additional queries used for the union.

Click “View SQL” to review the resulting SQL shown below.

SELECT Employees.last_name,
Employees.first_name,
Departments.dept_name
FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.dept_id
WHERE Departments.dept_name ='Sales'
UNION ALL
SELECT Employees.last_name,
Employees.first_name,
Departments.dept_name
FROM Employees INNER JOIN Departments ON Employees.dept_id = Departments.dept_id
WHERE Departments.dept_name = 'Marketing'
UNION ALL
SELECT Employees.last_name,
Employees.first_name,
Departments.dept_name
FROM Employees inner join Departments ON Employees.dept_id = Departments.dept_id
WHERE Departments.dept_name = 'Training'

Results

Executing the query as a Dashboard produces the results shown below in which the three queries are merged together to obtain employees from the Sales, Marketing, and Training Departments.