CODEWITHSUNDEEP
postgresqljoin
Harry G.
Harry G.

Reputation: 45

IN operator in join predicate

I am trying to join 2 tables in Postgresql. Col1 in table1 is of type character varying and col2 in table2 is of type character varying[]

Does anybody know why using the IN operator does not work?

This is the query:

SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.col1 in t2.col2

and this is the error: ERROR: syntax error at or near "t2"

Also, SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.col1 in unnest(t2.col2)

fails with ERROR: syntax error at or near "unnest"

Upvotes: 0

Views: 252

Answers (1)

khampson
khampson

Reputation: 15356

With the code you currently have, both sides of the IN expression are single values. With IN, the right-side would be expected to be an array or multi-valued expression, as opposed to a singular value.

If they really are single values, then what you would want is this:

SELECT t1.*, t2.*
FROM table1 t1 INNER JOIN table2 t2 ON t1.col1 = t2.col2;

If there really was a list expression for the right-hand side, you could do something like this:

SELECT t1.*, t2.*
FROM table1 t1 INNER JOIN table2 t2 ON t1.col1 IN (t2.col2, t2.col3);

Edit in response to comment from OP:

IN does not operate on arrays. Rather, it operates on rows. You could try using ANY instead, which does operate on arrays.

The doc describes IN as where:

The right-hand side is a parenthesized list of scalar expressions.

and ANY as where:

The right-hand side is a parenthesized expression, which must yield an array value.

See the Postgres doc for more details on IN and ANY, among others.

Upvotes: 1

Related Questions