Redshift WHERE Clause Multiple Columns Support

The relational databases such as Teradata and Oracle supports the multiple columns in WHERE clause. It is one of the main requirements when you are migrating from legacy databases such as Teradata to Amazon Redshift. In this article, we will check Redshift WHERE clause multiple columns support.

What is a WHERE Clause?

The WHERE clause contains conditions that either apply predicates to columns in tables or join multiple tables. Almost all modern databases allow you to join tables in WHERE clause.

The WHERE clause specifies a condition that matches a subset of rows or expression. You can use WHERE clause to filter the result of the FROM clause in a SELECT statement. You can also use WHERE clause to specify which rows to operate on in an UPDATE, MERGE, or DELETE .

Following is the WHERE clause syntax:

[ WHERE condition ]

In above syntax, the search condition can a Boolean result, such as a join condition or a predicate on a table column.

Is Multi column WHERE Clause Supported in Amazon Redshift?

Until recently, it was not supported in Amazon Redshift. But, now Redshift started supporting this one of the import features. You can use multiple columns in the WHERE clause to apply predicates to multiple columns in the table.

Following is the syntax:

[ WHERE (col1, col2) IN (value1, value2)]

In the above syntax, col1, col2 are table columns and value1 , value2 can be an expression of subquery columns.

Redshift with Multiple Columns in WHERE Clause Examples

The following show some simple uses of the WHERE clause.

select
	*
from
	inventory
where

	(inv_date_sk,
	inv_item_sk) in (
	select
		inv_date_sk,
		inv_item_sk
	from
		inventory_stg);

Redshift with Multiple Columns in WHERE Clause Alternative

You can correlate both outer and table within subquery on columns that are available in WHERE clause condition. You can use EXISTS condition to evaluate multiple columns in the WHERE clause. This method you can use if you are using a subquery.

The EXISTS conditions test for the existence of rows in a subquery, and return true if a subquery returns at least one row. If NOT is specified, the condition returns true if a subquery returns no rows.

For example,

select
	*
from
	inventory inv
where
	exists (
	select
		1
	from
		inventory_stg inv_stg
	where
		inv.inv_date_sk = inv_stg.inv_date_sk
		and inv.inv_item_sk = inv_stg.inv_item_sk
		);

The query that uses the EXISTS condition is considered as an optimized query.