SELECT Statement

Joins

Joins are operations where two tables are logically "joined" or linked together in a SQL statement using fields common to both tables. A join in a SQL statement allows data to be qualified and retrieved from one or more of these tables based on this link.

For example, in a database, every customer in a customers table will have a customer number or some other unique identifying field. The orders table will link each order to a specific customer by including a link field containing that unique customer id. Orders can then be retrieved for a specific customer.

Data retrieved from two tables that are not joined will result in a cartesian product. This can greatly inhibit query performance and distort aggregations.

If the WHERE clause criteria in an outer join references both tables and is 'AND'ed together, the query will be result in an inner join. For example, the following two SQL statements will return the same results:

Left Outer Join

SELECT ... FROM table1 LEFT JOIN table2 ON table1.linkfield = table2.linkfield
WHERE table1.column = criteria1 AND table2.column = criteria2

Inner Join

SELECT ... FROM table1, table2
WHERE table1.linkfield = table2.linkfield
AND table1.column = criteria1 AND table2.column = criteria2

Syntax

SELECT ... FROM table1
[ LEFT | RIGHT ] [OUTER] JOIN table2
ON table1.linkfield = table2.linkfield ...

or

SELECT ... FROM table1, table2
WHERE table1.linkfield = table2.linkfield ...

Omnidex does not support the SQL Server and Oracle specific outer join syntax:

WHERE table1.linkfield*=table2.linkfield
WHERE table1.linkfield=table2.linkfield(+)

Join Types

Omnidex supports three types of joins: inner joins, left outer joins, right outer joins.

Inner Joins

Most joins in an SQL SELECT statement, are inner joins. This means that two tables are joined together and rows are qualified for the left operand where related records exist in the right operand.

For example, when joining the customers table to the orders table, customers are only qualified when they have orders in the orders table. This eliminates all customers that do not have orders.

Inner joins can be specified in an SQL SELECT statement in a couple of ways.

One way is to use a JOIN statement in the FROM clause of an SQL SELECT statement.

SELECT p.acct, p.company, o.order_date
FROM prospects p
JOIN orders o ON p.acct = o.acct

Another way is to join the tables in a WHERE clause predicate.

SELECT p.acct, p.company, o.order_date
FROM prospects p, orders o
WHERE p.acct = o.acct

Both example produce the same results and are fully supported by Omnidex.

Left Outer Joins

A left outer join will qualify all rows in the left operand whether or not they have related records in the right operand.

For example, when using a left outer join on customers to orders, all customers and only orders that have related customer records will qualify. Null indicators will be returned for the orders fields where orders do not exist for a customer.

SELECT p.acct, p.company, o.order_date
FROM prospects p
LEFT JOIN orders o ON p.acct = o.acct

SELECT p.acct, p.company, o.order_date
FROM prospects p
LEFT OUTER JOIN orders o ON p.acct = o.acct

Both of these statements are identical. The OUTER keyword is optional.

The target table, orders in the above example, cannot be a UNION VIEW (see Partitioning). However, the UNION VIEW can be the FROM table, prospects above.

Right Outer Joins

Similar to left outer joins, a right outer join in the same scenario (see Left Outer Joins, above), will qualify all orders and only those customers that have related orders. Null indicators will be returned for the customer fields where a customer record does not exist for an order.

When encountering a right outer join, Omnidex will attempt to convert the join into a left outer join. For example:

SELECT p.acct, p.company, o.order_date
FROM orders o
RIGHT JOIN prospects p ON o.acct = p.acct

will be converted to:

SELECT p.acct, p.company, o.order_date
FROM prospects p
LEFT JOIN orders o ON p.acct = o.acct

Both statements return ALL prospects and their order dates if they exist or null indicators if they don't. The OUTER keyword is optional.

If Omnidex is unable to convert a right outer join to a left outer join, similar to the above example, the SQL statement will fail and return an error.

Examples

These examples demonstrate the difference between inner and outer joins.

Inner Join

This statement counts the number of distinct account numbers in the prospects table, where that account number also occurs at least once in the orders table. This means that three prospects also have orders.

SELECT count(distinct p.acct) FROM prospects p
JOIN orders o ON p.acct = o.acct

COUNT(DISTINCT PROSPECTS.ACCT)
------------------------------
3

1 row returned

Left Outer Join

This statement counts the number of distinct account numbers in the prospects table, whether or not they have orders in the orders table.

SELECT count(distinct p.acct) FROM prospects p
LEFT JOIN orders o ON p.acct = o.acct

COUNT(DISTINCT PROSPECTS.ACCT)
------------------------------
10

1 row returned

Inner Join

This statement returns the account number and company name from the prospects table and the order date from the orders table. Because this is an inner join, only prospects that have related records in the orders table will be returned.

SELECT p.acct, p.company, o.order_date FROM prospects p, orders o
WHERE p.acct = o.acct AND p.acct in (3,4)

1 row returned

Left Outer Join

The same statement using a left outer join returns an additional record. Because this is a left outer join, all prospects that qualify with the given criteria, acct=3 or acct=4, are returned and those that do not have orders in the orders table have a null indicator in the order_date field.

SELECT p.acct, p.company, o.order_date FROM prospects p, orders o
WHERE p.acct = o.acct AND p.acct in (3,4)

2 rows returned

Top