PostgreSQL WHERE IN Condition

In this PostgreSQL tutorial, we will learn about PostgreSQL WHERE IN Condition, the WHERE IN condition enables us to filter the query results depending on predefined values. We will understand the WHERE IN condition using different examples.

What is PostgreSQL WHERE IN Condition

In PostgreSQL, the WHERE IN condition is used to determine whether a value matches any value in the provided list or subquery. However it is frequently used with SELECT statements, it can also be used with the UPDATE and DELETE statements.

The syntax of the WHERE IN condition is as follows:

SELECT column1, column2, column3, ....
FROM table_name
WHERE column_name IN (value1, value2, ...);

The column_name indicates the column with which you want to compare it, and the values included in parentheses “WHERE column_name IN (value1, value2, …);” show the set of values you want to compare it to.

Let’s see with some examples how the WHERE IN condition works.

PostgreSQL WHERE IN Condition Simple Use

We are going to use the two tables “authors” and “books”, the “authors” table provides data on authors such as auther_id, auther_name, books_written, whereas the “books” table has data about books such as book_id, title, publication_year, auther_id and category_id.

Suppose we want to get all the books from the “books” table that were written by a particular author. To get this kind of result or data from the table, we can use the WHERE IN condition. use the below query.

SELECT title
FROM books
WHERE author_id IN (2, 4)

The above query returns the title of all the books where the author_id is either 2 or 4. This is how we can filter the data from the table using the WHERE IN condition.

PostgreSQL WHERE IN Condition with Subquery

You can also use the subqueries with the WHERE IN condition to create the list of values to match against dynamically. Now suppose we want to get the title of books that have been published more than three books. For that use the below query.

SELECT title
FROM books
WHERE author_id IN (
SELECT author_id
	FROM authors
	WHERE books_written > 3
);

The above query gets the titles of the books by authors who have written or published more than three books. It uses the subquery to get the author_id from the “authors” table where the “books_written” column is greater than 3.

The main query then gets the book titles from the table “books” where the subquery result set includes the author_id.

PostgreSQL WHERE IN Condition with Other Conditions

The WHERE IN can be used with conditions like AND or OR to make complex queries. For example, we want to get the books written by authors with a particular author_id and published after a particular year. For that use the below query.

SELECT title
FROM books
WHERE author_id = 2
 AND publication_year > 1990;

In the above query, we get the title of the books whose author_id is 2 and whose publication_year is more than 1990. Similar to other conditions, the WHERE IN conditions can be combined with others to further improve the result of your query.

PostgreSQL WHERE IN Condition with String Values to Filter

The WHERE IN condition can be used with string values in addition to numeric values. For example, we want to get the author_id based on the specific title of the books. For that use the below query.

SELECT author_id, title
FROM books
WHERE title IN ('War and Peace by Leo Tolstoy', 'The Odyssey by Homer');

In the above query, we get the authors_id and the title of the books where the title of the books is either “War and Peace by Leo Tolstoy” or “The Odyssey by Homer”. Based on the given string values, we can filter the result using the WHERE IN condition.

Conclusion

In this PostgreSQL tutorial, we have learned how to use the WHERE IN condition with subquery, and other conditions like AND or OR, and filter the results based on given string values with different examples.

You may like to read the following articles:

• PostgreSQL WHERE Clause
• How to Drop All Tables in PostgreSQL
• Postgres Export to CSV | Export PostgreSQL Table To CSV File