Equivalents to SQL queries

Overview

The projects in the repository folder Samples\Basic\Equivalents to SQL queries demonstrate the Data Management equivalents for these common SQL use patterns:

• Selecting and joining

• Join with inequality relation

• Alternatives to self-join

• Nested sub-queries

Selecting and joining

The sample project SQL equivalent1 demonstrates the Data Management equivalent of the following SQL query:

SELECT ed_lname, ed_fname, ed_pos
FROM editors, titleeditors
WHERE titleeditors.title_id = 'PC8888'
AND editors.ed_id = titleeditors.ed_id

This query finds the first name, last name, and position of all editors associated with the book whose ID is PC8888.

Join with inequality relation

The sample project SQL equivalent2 shows the Data Management equivalent of the following SQL query:

SELECT DISTINCT s.sonum, s.stor_id, s.sdate,
sd.date_shipped
FROM sales s, salesdetails sd
WHERE s.sdate < sd.date_shipped
AND s.sonum = sd.sonum

This query finds orders that were shipped on a date later than the sale date.

Alternatives to self-join

The sample project SQL equivalent3 shows two Data Management versions of the following SQL query:

SELECT DISTINCT t1.title_id, t1.au_id
FROM titleauthors t1, titleauthors t2
WHERE t1.title_id = t2.title_id
AND t1.au_id != t2.au_id
ORDER BY t1.title_id

This query finds all books that have more than one author.

Two examples are provided. The first example is a direct translation from SQL. The second example shows a simplified approach that removes the hard-to-understand self-join step.

Nested subqueries

The sample project SQL equivalent4 shows the Data Management equivalent of the following SQL query:

SELECT title, price
FROM titles
WHERE price = (SELECT min(price) FROM titles)

This query finds the title and price of all books whose price is equal to the lowest book price. It demonstrates how a separate data-flow branch that is later re-joined to the input table can be an equivalent to a sub-query in SQL.