An Existence (or Non-Existence) query checks for existence (or non-existence) of something. With an Existence/Non-Existence query, you are not interested in the results of the query—only if results are returned for the query. This is the key concept behind Existence /Non-Existence queries. The data returned by the query is not important, only whether such data exists.
Because you do not care about any particular data, you do not need to select any fields. If you were writing SQL code, you would use an asterisk (*) as your SELECT list. In SQR Production Reporting Studio, creating an Existence/Non-Existence query automatically generates the asterisk in the resulting SQL.
As an example, assume that you would like to see which customers did not place orders between January 1, 1998 and January 1, 1999. To do this, you would:
Create a query to get a list of customers. (This is the outer query.) The resulting SQL would look like:
SELECT name, state, phone FROM customers ORDER BY name
Create a second query to determine if there are any orders between the two dates. (This is the inner query.) The resulting SQL would look like:
SELECT * FROM orders WHERE order_date BETWEEN '1998-01-01' AND '1999-01-01'
For each customer returned by the first (or outer) query, we want to run the second (or inner) query to find any orders. Running one query for each row returned from another query is called correlation. Existence/Non-Existence queries are almost always correlated. To find just the orders for a specific customer, we need to correlate the queries on the customer number. The resulting SQL looks like:
SELECT c.name, c.state, c.phone FROM customers c WHERE NOT EXISTS (SELECT * FROM orders WHERE order_date BETWEEN '1998-01-01' AND '1999-01-01' AND orders.cust_num = c.cust_num) ORDER BY c.name
See Creating the Outer Query and Creating an Existence Test Subquery for information on how to useSQR Production Reporting Studio to create the SQL in the preceding example.
You can create the queries for the example discussed here using the sample data loaded into your database with the (see LoadAll program.