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 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. |
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.
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 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:
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 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:
Example
The construction below will make a join on the specified column id.
SELECT * FROM epmloyees JOIN salaries USING (id)
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:
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.
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.
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:
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
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:
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
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:
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
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:
Example
SELECT * FROM
(SELECT COUNT(*) AS store_count FROM stores) s
CROSS JOIN
(SELECT COUNT(*) AS emp_count FROM employees) e
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. |