Appearance

Reference>Meta Database>SQL Command Reference>Query Syntax>WHERE

WHERE ​

The WHERE clause contains conditions that can be used to:

  • Filter the results of the FROM clause of a SELECT statement
  • Define the rows to be processed for UPDATE, MERGE, or DELETE clauses.

Syntax ​

sql
...
WHERE expr
[ ... ]

Parameters ​

expr: A boolean expression. Logical operators are also allowed in the expression.

Usage Notes ​

  • The conditions in the WHERE clause are evaluated after the FROM clause (thus reordering by the optimizer does not affect the results). Example: A WHERE clause that references columns of an object defined as part of an OUTER join in the FROM clause filters the rows returned by the OUTER join (which may also contain NULL values).
  • When handling NULL values, note that:
    • Usually, NULL = NULL results in NULL and not TRUE, which can be confusing. It is therefore recommended to use the IS [ NOT ] NULL operator to compare NULL values.
    • If the result of evaluating a condition in the WHERE clause is NULL, the row is removed (filtered out) from the result set.
  • The maximum possible number of expressions is 16,384. For a very large number of conditions, consider creating an additional table containing values for a JOIN and linking this table with the table to be filtered, instead of using an IN() clause.

Joins in the WHERE Clause ​

The WHERE clause can be used to express JOIN operations.

For general information on the JOIN concept, see the User Guide to Joins.

The WHERE clause specifies boolean expressions that define the rows of objects to be joined.

Example

The following queries are equivalent in terms of results:

sql
-- 1
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
     )
SELECT *
    FROM t1, t2
         WHERE t2.col1 = t1.col1;
+------+-------+------+------+
| COL1 | COL12 | COL1 | COL2 |
+------+-------+------+------+
| 1    | 2     | 1    | 20   |
| 2    | 3     | 2    | 30   |
+------+-------+------+------+

and

sql
-- 2
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
     )
SELECT *
    FROM t1 
         INNER JOIN t2 
            ON t2.col1 = t1.col1;
+------+-------+------+------+
| COL1 | COL12 | COL1 | COL2 |
+------+-------+------+------+
| 1    | 2     | 1    | 20   |
| 2    | 3     | 2    | 30   |
+------+-------+------+------+

OUTER joins can be specified in the WHERE clause using the (+) operator. The + operator is applied to each join column of the table that is on the "inner" side.

Note

The result of any OUTER join contains a copy of all rows from one of the objects, which represents the "outer" object, and the other object represents the "inner" object.

Thus, in a LEFT OUTER JOIN, the left object is the "outer" and the right object is the "inner". In a RIGHT OUTER JOIN, the right object is the "outer" and the left object is the "inner".

Examples

The following queries are equivalent in terms of results:

sql
-- 1
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL
       SELECT 3 AS col1, 4 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 4 AS col1, 40 AS col2
     )
SELECT *
    FROM t1, t2
         WHERE t1.col1 = t2.col1(+);
+------+-------+------+------+
| COL1 | COL12 | COL1 | COL2 |
+------+-------+------+------+
| 1    | 2     | 1    | 20   |
| 2    | 3     | 2    | 30   |
| NULL | 4     | NULL | NULL |
+------+-------+------+------+

and

sql
-- 2
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL
       SELECT 3 AS col1, 4 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 4 AS col1, 40 AS col2
     )
SELECT *
    FROM t1 
         LEFT OUTER JOIN t2 
            ON t1.col1 = t2.col1;
+------+-------+------+------+
| COL1 | COL12 | COL1 | COL2 |
+------+-------+------+------+
| 1    | 2     | 1    | 20   |
| 2    | 3     | 2    | 30   |
| NULL | 4     | NULL | NULL |
+------+-------+------+------+

Example

In this example, objects are joined on multiple columns. The (+) operator marks the columns of the "inner" objects.

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 50 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 4 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t1.col12 AS t1_col2, t2.col1 AS t2_col1, t2.col2 AS t2_col2
    FROM t1, t2 
         WHERE t1.col1 = t2.col1(+) AND t1.col12 = t2.col2(+)
         ORDER BY 1, 3;
+---------+---------+---------+---------+
| T1_COL1 | T1_COL2 | T2_COL1 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 20      | 1       | 20      |
| 2       | 3       | NULL    | NULL    |
| 3       | 4       | NULL    | NULL    |
| 4       | 50      | 4       | 50      |
+---------+---------+---------+---------+

and

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 50 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 4 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t1.col12 AS t1_col2, t2.col1 AS t2_col1, t2.col2 AS t2_col2
    FROM t1 
         LEFT OUTER JOIN t2 
         ON t1.col1 = t2.col1 AND t1.col12 = t2.col2
         ORDER BY 1, 3;
+---------+---------+---------+---------+
| T1_COL1 | T1_COL2 | T2_COL1 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 20      | 1       | 20      |
| 2       | 3       | NULL    | NULL    |
| 3       | 4       | NULL    | NULL    |
| 4       | 50      | 4       | 50      |
+---------+---------+---------+---------+

(+) can also be used to identify different objects as "inner" objects in different joins of the same SQL statement.

Example

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
      ), 
t2 AS (SELECT 1 AS col11, 20 AS col12
      UNION ALL 
      SELECT 2 AS col11, 30 AS col12
      ),
t3 AS (SELECT 1 AS col21, 320 AS col22
      UNION ALL 
      SELECT 2 AS col21, 330 AS col22
      )
SELECT *
    FROM t1, t2, t3
      WHERE t1.col1 = t2.col11 (+)
        AND t1.col1 = t3.col21 (+);
+------+------+-------+-------+-------+-------+
| COL1 | COL2 | COL11 | COL12 | COL21 | COL22 |
+------+------+-------+-------+-------+-------+
| 1    | 20   | 1     | 20    | 1     | 320   |
| 2    | 3    | 2     | 30    | 2     | 330   |
+------+------+-------+-------+-------+-------+

The (+) operator can be placed either immediately next to the object column name or separated by a space from the object column name. Example: Both notations are valid: where t1.col1 = t2.col2(+) and where t1.col1 = t2.col2 (+).

It is also possible to combine the (+) operator in the WHERE clause with the FROM ... ON ... clause in the same query.

Limitations of the (+) Operator in the WHERE Clause ​

  • Creating a FULL OUTER JOIN is not possible with the (+) operator.

Example

The following query results in an error:

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 50 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 4 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t1.col12 AS t1_col2, t2.col1 AS t2_col1, t2.col2 AS t2_col2
    FROM t1, t2 
         WHERE t1.col1(+) = t2.col1(+) 
         ORDER BY 1, 3;
Error: a predicate may reference only one outer-joined table
 at my_metafolder.my_query (1:1)
  • If an object is involved in multiple JOINs, it is possible with the (+) notation to specify the object as the "inner" object in only one of these joins.

Example

The following query results in an error:

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      ),
t3 AS (SELECT 1 AS col1, 320 AS col2
      UNION ALL 
      SELECT 2 AS col1, 330 AS col2
      )
SELECT *
    FROM t1, t2, t3
      WHERE t1.col1 (+) = t2.col1
        AND t1.col1 (+) = t3.col1
         ORDER BY 1, 3;
Error: a table may be outer joined to at most one other table
 at my_metafolder.my_query (1:1)

Example 1 | Filtering ​

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col2
       UNION ALL
       SELECT 2 AS col1, 25 AS col2
       UNION ALL
       SELECT 3 AS col1, 45 AS col2
       UNION ALL
       SELECT 4 AS col1, 12 AS col2
      )
SELECT *
    FROM t1 
        WHERE col1 = 1;
+------+------+ 
| COL1 | COL2 | 
+------+------+ 
| 1    | 20   | 
+------+------+

Example 2 | Filtering with Multiple Conditions ​

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col2
       UNION ALL
       SELECT 2 AS col1, 25 AS col2
       UNION ALL
       SELECT 3 AS col1, 45 AS col2
       UNION ALL
       SELECT 4 AS col1, 12 AS col2
      ) 
SELECT *
    FROM t1 
        WHERE col2 <= 25
          AND col1 > 1;
+------+------+ 
| COL1 | COL2 | 
+------+------+ 
| 2    | 25   | 
| 4    | 12   | 
+------+------+

Example 3 | Filtering with a Subquery ​

sql
WITH
t1 AS (SELECT 1 AS col1, 20 AS col2
       UNION ALL
       SELECT 2 AS col1, 25 AS col2
       UNION ALL
       SELECT 3 AS col1, 45 AS col2
       UNION ALL
       SELECT 4 AS col1, 12 AS col2
      ) 
SELECT *
    FROM t1 
        WHERE col2 <= (SELECT AVG(col2)
                          FROM t1
                            );
+------+------+ 
| COL1 | COL2 | 
+------+------+ 
| 1    | 20   | 
| 2    | 25   | 
| 4    | 12   | 
+------+------+

Example 4 | Joining with WHERE ​

sql
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
     )
SELECT *
    FROM t1, t2
        WHERE t2.col1 = t1.col1
        ORDER BY 1, 2;
+------+-------+------+------+ 
| COL1 | COL12 | COL1 | COL2 | 
+------+-------+------+------+ 
| 1    | 2     | 1    | 20   | 
| 2    | 3     | 2    | 30   | 
+------+-------+------+------+

Example 5 | Inner Join of Multiple Objects with WHERE ​

sql
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 5 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      ),
t3 AS (SELECT 1 AS col1, 320 AS col32
      UNION ALL 
      SELECT 3 AS col1, 350 AS col32
      UNION ALL 
      SELECT 5 AS col1, 360 AS col32
      UNION ALL 
      SELECT 6 AS col1, 370 AS col32
      )
SELECT t1.col1  AS t1_col1, 
       t2.col1  AS t2_col1,
       t3.col1  AS t3_col1,
       t1.col12 AS t1_col2,
       t2.col2  AS t2_col2,
       t3.col32 AS t3_col2
    FROM t1, t2, t3
        WHERE t1.col1  = t2.col1 
          AND t2.col1  = t3.col1
      ORDER BY 3, 2, 1;
+---------+---------+---------+---------+---------+---------+ 
| T1_COL1 | T2_COL1 | T3_COL1 | T1_COL2 | T2_COL2 | T3_COL2 | 
+---------+---------+---------+---------+---------+---------+ 
| 1       | 1       | 1       | 2       | 20      | 320     | 
| 3       | 3       | 3       | 4       | 50      | 350     | 
+---------+---------+---------+---------+---------+---------+

Example 6 | Outer Join of Multiple Objects with WHERE ​

sql
WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 5 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      ),
t3 AS (SELECT 1 AS col1, 320 AS col32
      UNION ALL 
      SELECT 3 AS col1, 350 AS col32
      UNION ALL 
      SELECT 5 AS col1, 360 AS col32
      UNION ALL 
      SELECT 6 AS col1, 370 AS col32
      )
SELECT t1.col1  AS t1_col1, 
       t2.col1  AS t2_col1,
       t3.col1  AS t3_col1,
       t1.col12 AS t1_col2,
       t2.col2  AS t2_col2,
       t3.col32 AS t3_col2
    FROM t1, t2, t3
        WHERE t1.col1(+) = t2.col1 
          AND t2.col1    = t3.col1(+)
      ORDER BY 3, 2, 1;
+---------+---------+---------+---------+---------+---------+ 
| T1_COL1 | T2_COL1 | T3_COL1 | T1_COL2 | T2_COL2 | T3_COL2 | 
+---------+---------+---------+---------+---------+---------+ 
| 1       | 1       | 1       | 2       | 20      | 320     | 
| 3       | 3       | 3       | 4       | 50      | 350     | 
| NULL    | 5       | 5       | NULL    | 60      | 360     | 
| 2       | 2       | NULL    | 3       | 30      | NULL    | 
+---------+---------+---------+---------+---------+---------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.