Search Conditions and Joins

This chapter discusses search conditions, that is, composite predicates defining row subsets from tables; and joined tables and inner and outer join specifications.

Search Conditions

Search conditions are used in:

WHERE and HAVING clauses to qualify the selection of rows and groups respectively

CHECK clauses to define sets of acceptable values

CASE expressions to conditionally return different values

CASE and IF statements to control conditional execution in a routine or trigger

WHILE and REPEAT statements to control conditional iteration in a routine or trigger

the WHEN clause of a trigger to control conditional execution of the trigger action.

A search condition is built from one or more predicates linked by the logical operators AND and OR and qualified if desired by the operator NOT.

A search condition is a boolean returning expression. (See Expressions.)

Rules

Search conditions enclosed in parentheses may be used as part of more complex search condition constructions. A search condition is evaluated as follows:

Conditions in parentheses are evaluated first.

Within the same level of parentheses, NOT is applied before AND, AND is applied before OR.

Operators at the same precedence level are applied in an order determined by internal optimization routines.

The result of a search condition is evaluated by combining the results of the component predicates. Each predicate evaluates to true, false or unknown, truth tables are shown in Truth Tables.

WHERE and HAVING clauses select the set of values for which the search condition evaluates to true. CHECK clauses define the set of values for which the search condition does not evaluate to false, i.e. is either true or unknown.

 

Examples

Using WHERE

The WHERE condition determines which rows to select, for example:

SELECT *

FROM customer_details

WHERE country_code = 'SE'

Using HAVING

The HAVING clause restricts the selection of groups. A HAVING clause may contain set functions in the search condition. See Mimer SQL User's Manual, Grouped Set Functions – the GROUP BY Clause.

Standard Compliance

This section summarizes standard compliance concerning search conditions.

Standard

Compliance

Comments

SQL-2016

Core

Fully compliant.

Joined Tables

The JOIN syntax provides methods of combining information in tables.

INNER JOINs and OUTER JOINs are supported.

The different ways in which the various join options can be combined makes the overall JOIN syntax quite convoluted, so to simplify the explanation, each variant will be described on its own with an accompanying syntax diagram.

In order to understand the JOIN syntax generally, it is important to appreciate the difference between an INNER JOIN and an OUTER JOIN. It is also important to be aware of the difference between JOIN ON, JOIN USING and NATURAL JOIN.

INNER JOINs

An INNER JOIN produces a result table containing composite rows created by combining rows from two tables where some pre-defined, or explicitly specified, join condition evaluates to true.

Rows that do not satisfy the JOIN condition will not appear in the result table of an INNER JOIN.

The INNER JOIN is the default JOIN type, and the INNER keyword is optional and may be omitted.

JOIN ON

JOIN ON allows a join condition to be specified. The result table of this kind of join is produced by applying the specified join condition to the Cartesian product of the two tables. The result table will contain only those rows for which the join condition evaluates to true.

The join condition cannot reference common columns unless they are qualified (e.g. by table name).

A row in the result table contains the combined set of columns from each table. The columns from table-reference-1 appear first followed by those from table-reference-2. Common columns will therefore appear twice.

Syntax

The syntax for JOIN ON is:

JOIN_ON.png

 

Example

The ON condition is used to select the employees’ corresponding row(s) salaries, and the WHERE condition is used to find those whose salaries are less than 10 000.

SELECT *

FROM epmloyees INNER JOIN salaries ON epmloyees.id = salaries.id

WHERE salaries.salary < 10000

JOIN USING

JOIN USING allows a list of common column names to be specified. The result contains one row for each case where all the specified columns in the two tables contain values that are equal. (This kind of join is conceptually the same as a NATURAL JOIN except that the join is based on the specified columns rather than on all the common columns.)

Specifying the columns explicitly instead of using the entire set of common columns is useful in situations where some of the common columns may not contain identical values even though the respective rows are related (e.g. as in a REMARKS column).

The columns specified in the list must be common to both tables, they must be specified in an unqualified manner and must have a data type that allows the values in the respective tables to be compared.

A row in the result table contains the combined set of columns from each table, except that the common columns appear only once. The columns specified in the list of column names appear first (at the left of the table) followed by the remaining columns from table-reference-1, followed by those from table-reference-2.

Syntax

The syntax for JOIN USING is:

JOIN_USING.png

 

Example

The construction below will make a join on the specified column id.

SELECT * FROM epmloyees JOIN salaries USING (id)

NATURAL JOIN

The result table of a NATURAL JOIN contains one row for each case where all the common columns in the two tables contain values that are equal.

Common columns are those which have the same name in each table. The common columns must have a data type that allows values in the respective tables to be compared.

A row in the result table contains the combined set of columns from each table, except that the common columns appear only once. The common columns appear first (at the left of the table) followed by the remaining columns from table-reference-1, followed by those from table-reference-2.

Syntax

The syntax for a NATURAL JOIN is:

NATURAL_JOIN.png

 

If there are no rows where all the common columns have equal values, the result table is an empty table (i.e. it has a set of columns as just described, but the number of rows is zero).

For example, two tables contain different sets of information on people, and each table has a FIRST_NAME column and a SURNAME column to identify the person to whom the information applies.

When both the FIRST_NAME column and the SURNAME column contain the same values in a row in each table, it means those rows are related. A NATURAL JOIN between these two tables would produce a result table with a single composite row for each person, containing all the information held in both tables, with the SURNAME and FIRST_NAME columns appearing once in the rows of the result.

Note:It is actually possible to perform a NATURAL JOIN between two tables which have no common columns at all. In this case the result table is the Cartesian product (sometimes called the CROSS JOIN) of the two tables.

Examples

The NATURAL INNER JOIN construction below will join the employees and salaries tables on all columns which share the same name.

SELECT * FROM employees NATURAL JOIN salaries

 

Note:Use of NATURAL JOIN is discouraged in programs since subsequent alterations in the table or view definitions may result in an additional join condition (e.g. if a column with the same name as the new column already exists in the other table), and cause the program to function incorrectly.

OUTER JOINs

A table resulting from an inner join, as just described, will only contain those rows that satisfy the applicable join condition. This means that a row in either table which does not match a row in the other table will be excluded from the result.

In an OUTER JOIN, however, a row that does not match a row in the other table is also included in the result table. Such a row appears once in the result and the columns that would normally contain information from the other table will contain the null value.

The join variants (JOIN ON, NATURAL JOIN and JOIN USING) can be applied as OUTER JOINs as well.

The OUTER keyword is optional and may be omitted.

LEFT OUTER JOIN

In addition to the INNER JOIN result, the LEFT OUTER JOIN also includes the rows from table-reference-1 (the table on the left of the JOIN) which do not satisfy the join condition.

Syntax

The syntax variants of the LEFT OUTER JOIN are as follows:

left_join.png

 

left_join_using.png

 

natural_left_join.png

 

 

Example

The query below will return a result set containing all employees at least once even though they might not have an entry in the SALARIES table.

SELECT *

FROM employees

LEFT JOIN salaries

   ON employees.id = salaries.id

RIGHT OUTER JOIN

In addition to the INNER JOIN result, the RIGHT OUTER JOIN also includes the rows from table-reference-2 (the table on the right of the JOIN) which do not satisfy the join condition.

Syntax

The syntax for the variants of the RIGHT OUTER JOIN is as follows:

 

right_join.png
right_join_using.png

 

natural_right_join.png

 

 

Example

The query below counts the number of releases for each month during the year 1990, using RIGHT OUTER JOIN to include months without any release.

select smr.c as month_no, count(i.item_id) as number_of_releases

from items i

right join system.manyrows smr

   on  extract(month from i.release_date) = smr.c

   and extract(year from i.release_date) = 1990

where smr.c between 1 and 12

group by smr.c

FULL OUTER JOIN

A FULL OUTER JOIN combines the effect of both a LEFT JOIN and a RIGHT JOIN, i.e. the FULL OUTER JOIN includes the rows from the left table which do not satisfy the join condition, and the rows from the right table which do not satisfy the join condition.

Syntax

The syntax for the variants of the FULL OUTER JOIN is as follows:

full_outer_join.png
full_outer_join_using.png

 

natural_full_outer_join.png

 

Example

A full outer join of the SELLERS table with the SUPPLIERS table on the CITY column:

SELECT sellers.seller_name, suppliers.supplier_name

FROM sellers

FULL OUTER JOIN suppliers

   ON sellers.city = suppliers.city

CROSS JOIN

A CROSS JOIN is basically an INNER JOIN between two tables without a join condition. The result table is the Cartesian product of the two tables.

Syntax

The syntax for CROSS JOIN is as follows:

cross_join.png

 

Example

SELECT * FROM

(SELECT COUNT(*) AS store_count FROM stores)

CROSS JOIN

(SELECT COUNT(*) AS emp_count FROM employees)

Standard Compliance

This section summarizes standard compliance concerning JOIN.

Standard

Compliance

Comments

SQL-2016

Core

Fully compliant.

SQL-2016

Features outside core

Feature F401 “Extended joined table”, NATURAL JOIN, FULL OUTER JOIN and CROSS JOIN.