WHERE Clause
The WHERE clause specifies criteria that restrict the contents of the results table. You can test for simple relationships or, using subselects, for relationships between a column and a set of columns.
Using a simple WHERE clause, the contents of the results table can be restricted, as follows:
Comparisons:
SELECT emp_name FROM employee
WHERE manager = 'Jones';
SELECT emp_name FROM employee
WHERE salary > 50000;
Ranges:
SELECT ordnum FROM orders
WHERE odate BETWEEN DATE('2014-01-01') AND CURRENT_DATE;
Set membership:
SELECT * FROM orders
WHERE partno IN ('123-45', '678-90');
Pattern matching:
SELECT * FROM employee
WHERE emp_name LIKE 'A%';
Nulls:
SELECT emp_name FROM employee
WHERE dept_no IS NULL;
Combined restrictions using logical operators:
SELECT emp_name FROM employee
WHERE dept_no IS NULL AND
hiredate = CURRENT_DATE;
Note: Aggregate functions cannot appear in a WHERE clause.
More information:
Joins
Joins combine information from multiple tables and views into a single result table, according to column relationships specified in the WHERE clause.
For example, given the following two tables:
Employee Table
Department Table
The following query joins the two tables on the relationship of equality between values in the edeptno and ddeptno columns. The result is a list of employees and the names of the departments in which they work:
SELECT ename, dname FROM employees, departments
WHERE edeptno = ddeptno;
A table can be joined to itself using correlation names; this is useful when listing hierarchical information. For example, the following query displays the name of each employee and the name of the manager for each employee.
SELECT e.ename, m.ename
FROM employees e, employees m
WHERE e.mgr = m.eno
Tables can be joined on any number of related columns. The data types of the join columns must be comparable.
ANSI/ISO Join Syntax
In addition to performing joins using the approach described in the Joins section, ANSI syntax described in the 1992 ANSI/ISO SQL standard can be used. The ANSI syntax provides a more precise way of specifying joins that are otherwise identical to those produced from the traditional syntax. The ANSI syntax also allows the specification of outer joins.
An outer join returns not only the rows of the join sources that join together according to a specified search condition, but also rows from one or both sources that do not have a matching row in the other source. For rows included in the outer join that do not have a matching row from the other source, null values are returned in all columns of the other source.
An outer join is the union of two SELECT statements: the first query returns rows that fulfill the join condition and the second query returns nulls for rows that do not fulfill the join condition.
The ANSI syntax is specified in the FROM clause, as follows:
source join_type JOIN source ON search_condition
or
source join_type JOIN source USING (column {,column})
or
source CROSS JOIN source
where:
source
Specifies the table, view, or join where the data for the left or right side of the join originates.
join_type
Specifies the type of join as one of the following:
INNER
(Default) Specifies an inner join.
LEFT [OUTER]
Specifies a left outer join, which returns all values from the left source.
RIGHT [OUTER]
Specifies a right outer join, which returns all values from the right source.
FULL [OUTER]
Specifies a full outer join, which returns all values from both left and right sources.
Note: RIGHT and LEFT joins are the mirror image of each other: (table1 RIGHT JOIN table2) returns the same results as (table2 LEFT JOIN table1).
ON search_condition
Is a valid restriction, subject to the rules for the WHERE clause. The search_condition must not include aggregate functions or subselects. Matching pairs of rows in the join result are those that satisfy the search_condition.
USING (column {,column})
Is an alternate form of the search_condition. Each column in the USING clause must exist unambiguously in each join source. An ON search_condition is effectively generated in which the search_condition compares the columns of the USING clause from each join source.
CROSS JOIN
Is a cross product join of all rows of the join sources.
By default, joins are evaluated left to right. To override the default order of evaluation, use parentheses. A join source can itself be a join, and the results of joins can be joined with the results of other joins, as illustrated in the following pseudocode:
(A join B) join (C join D)
The placement of restrictions is important in obtaining correct results. For example:
A join B on cond1 and cond2
does not return the same results as:
A join B on cond1 where cond2
In the first example, the restriction determines which rows in the join result table are assigned null values; in the second example, the restriction determines which rows are omitted from the result table.
The following examples are identical and use an outer join in the FROM clause to display all employees along with the name of their department, if any. One uses the ON clause and the other uses an equivalent USING clause:
SELECT e.ename, d.dname FROM
(employees e LEFT JOIN departments d
ON e.dept = d.dept);
SELECT e.ename, d.dname FROM
(employees e LEFT JOIN departments d
USING (dept));
Outer Joins
Data can be combined from two or more tables to produce an intermediate results table using an outer join.
Notes:
• Outer join functionality is available only if OUTER_JOIN is set to Y in the iidbcapabilities table.
• Outer joins specified in the FROM clause are not the same as joins specified in the WHERE clause: the FROM clause specifies sources of data, while the WHERE clause specifies restrictions to be applied to the sources of data to produce the results table.
Outer joins are specified in the FROM clause, using the following syntax:
source join_type join source
on search_condition
where:
• The source parameter is the table, view, or outer join where the data for the left or right side of the join originates.
• The join_type parameter specifies INNER, LEFT, RIGHT, or FULL outer join. The default join type is INNER.
• The search_condition is a valid restriction, subject to the rules for the WHERE clause. The search condition must not include aggregate functions or subselects.
Think of an outer join is as the union of two SELECT statements: the first query returns rows that fulfill the join condition, and the second returns nulls for rows that do not.
There are three types of outer joins:
• Left outer join—Returns all values from the left source
• Right outer join—Returns all values from the right source
• Full outer join—Returns all values from both sources
Note: Right and left joins are symmetrical: (table1 right-join table2) returns the same results as (table2 left-join table1).
By default, joins are evaluated left to right. To override the default order of evaluation, use parentheses.
A source can itself be an outer join, and the results of joins can be joined with the results of other joins, as illustrated in the following pseudocode:
(A join B) join (C join D)
The placement of restrictions is important in obtaining correct results. For example:
A join B on cond1 and cond2
does not return the same results as:
A join B on cond1 where cond2
In the first example, the restriction determines which rows in the join result table will be assigned null values; in the second example, the restriction determines which rows will be omitted from the result table.
The following example uses an outer join in the FROM clause to display all employees along with the name of their department, if any:
select e.ename, d.dname from
(employees e left join departments d
on e.edept = d.ddept);
Limitations and Restrictions on OUTER JOIN and NATURAL JOIN
OUTER JOIN is a subset of the ANSI SQL92 JOIN statement. It includes LEFT, RIGHT, and FULL JOIN. ANSI JOIN also includes:
• NATURAL [INNER] JOIN
• NATURAL LEFT [OUTER] JOIN
• NATURAL RIGHT [OUTER] JOIN
• NATURAL FULL [OUTER] JOIN
The keyword OUTER is optional for Ingres, SQL Server, Oracle, and DB2UDB databases.
At this time, only Oracle has implemented NATURAL JOIN. Because Ingres does not support NATURAL JOIN, it is not part of OpenSQL. Until Ingres supports it, NATURAL JOIN will not be implemented in gateways.
Oracle’s OUTER JOIN syntax is similar to that of Ingres.
USING Clause Support
Because SQL Server and DB2UDB do not support the USING clause in outer joins, USING clauses are passed through, and it is left to the underlying DBMS to determine whether the statement can be executed. If the database cannot execute the statement, it should report an error.
Join Relationships
The simple joins illustrated in the two preceding examples depend on equal values in the join columns. This type of join is called an equijoin. Other types of relationships can be specified in a join. For example, the following query lists salespersons who have met or exceeded their sales quota:
SELECT s.name, s.sales_ytd
FROM sales s, quotas q
WHERE s.empnum = d.empnum AND
s.sales_ytd >= d.quota;
Subselects
Subselects (also known as subqueries) are SELECT statements placed in a WHERE or HAVING clause. The results returned by the subselect are used to evaluate the conditions specified in the WHERE or HAVING clause.
Subselects must return a single column, and cannot include an ORDER BY or UNION clause.
The following example uses a subselect to display all employees whose salary is above the average salary:
SELECT * FROM employees WHERE salary >
(SELECT avg(salary) FROM employees);
In the preceding example, the subselect returns a single value: the average salary. Subselects can also return sets of values. For example, the following query returns all employees in all departments managed by Barth.
SELECT ename FROM employees WHERE edept IN
(SELECT ddept FROM departments
WHERE dmgr = 'Barth');
For details about the operators used in conjunction with subqueries, see
Predicates on page 35.