PostgreSQL WHERE IN

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.

PostgreSQL WHERE IN

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.

Syntax

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.

Example-1 Basic operation

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 written by a particular author from the “books” table. To get this kind of result or data from the table, we can use the WHERE IN condition. Use the query below.

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

After executing the above query, I got the expected output shown in the screenshot below. The above query returns the title of all the books with author_ids of 2 or 4.

Example-2 Using Subquery

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

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

After executing the above query, I got the expected output, which is shown in the screenshot below.

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.

Example-3 Using Multiple 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 specific year. For that, use the query below.

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 improve the result of your query further.

After executing the above query, I got the expected output, as shown in the below screenshot.

Example-4 Using the String Values to Filter

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

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

After executing the above query, I got the expected output, as shown in the screenshot below.

In the above query, we get the authors’ ID and the book titles, which are either “War and Peace by Leo Tolstoy” or “The Odyssey by Homer.” We can filter the result using the WHERE IN condition based on the given string values.

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