Movatterモバイル変換

[0]ホーム
URL:

You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial
/Getting Started/ANY Operator

PostgreSQL ANY Operator

Summary: in this tutorial, you will learn how to use the PostgreSQLANY operator to compare a scalar value with a set of values returned by a subquery.

Introduction to PostgreSQL ANY operator

The PostgreSQLANY operator compares a value with a set of values returned by asubquery. It is commonly used in combination with comparison operators such as =, <, >, <=, >=, and <>.

Here’s the basic syntax of  theANY operator:

expression operator ANY(subquery)

In this syntax:

  • expression is a value that you want to compare.
  • operator is a comparison operator including =, <, >, <=, >=, and <>.
  • subquery is a subquery that returns a set of values to compare against. It must return exactly one column.

TheANY operator returnstrue if the comparison returnstrue for at least one of the values in the set, andfalse otherwise.

If the subquery returns an empty set, the result ofANY comparison is alwaysfalse.

Besides the subquery, you can use any construct that returns a set of values such as anARRAY.

Note thatSOME is a synonym forANY, which means that you can use them interchangeably.

PostgreSQL ANY operator examples

Let’s take some examples of using theANY operator.

Setting up a sample table

First, create a table calledemployees andmanagers, and insert some data into it:

CREATE TABLE employees (    idSERIAL PRIMARY KEY,    first_nameVARCHAR(255)NOT NULL,    last_nameVARCHAR(255)NOT NULL,    salaryDECIMAL(10,2)NOT NULL);CREATE TABLE managers(    idSERIAL PRIMARY KEY,    first_nameVARCHAR(255)NOT NULL,    last_nameVARCHAR(255)NOT NULL,    salaryDECIMAL(10,2)NOT NULL);INSERT INTO employees (first_name, last_name, salary)VALUES('Bob','Williams',45000.00),('Charlie','Davis',55000.00),('David','Jones',50000.00),('Emma','Brown',48000.00),('Frank','Miller',52000.00),('Grace','Wilson',49000.00),('Harry','Taylor',53000.00),('Ivy','Moore',47000.00),('Jack','Anderson',56000.00),('Kate','Hill',44000.00),('Liam','Clark',59000.00),('Mia','Parker',42000.00);INSERT INTO managers(first_name, last_name, salary)VALUES('John','Doe',60000.00),('Jane','Smith',55000.00),('Alice','Johnson',58000.00);

Second, retrieve the data from theemployees table:

SELECT * FROM employees;

Output:

id | first_name | last_name |  salary----+------------+-----------+----------  1 | Bob        | Williams  | 45000.00  2 | Charlie    | Davis     | 55000.00  3 | David      | Jones     | 50000.00  4 | Emma       | Brown     | 48000.00  5 | Frank      | Miller    | 52000.00  6 | Grace      | Wilson    | 49000.00  7 | Harry      | Taylor    | 53000.00  8 | Ivy        | Moore     | 47000.00  9 | Jack       | Anderson  | 56000.00 10 | Kate       | Hill      | 44000.00 11 | Liam       | Clark     | 59000.00 12 | Mia        | Parker    | 42000.00(12 rows)

Third, retrieve the data from themanagers table:

SELECT * FROM managers;

Output:

id | first_name | last_name |  type   |  salary----+------------+-----------+---------+----------  1 | John       | Doe       | manager | 60000.00  2 | Jane       | Smith     | manager | 55000.00  3 | Alice      | Johnson   | manager | 58000.00(3 rows)

1) Using ANY operator with the = operator example

The following statement uses the ANY operator to find employees who have the salary the same as manager:

SELECT  *FROM  employeesWHERE  salary = ANY (    SELECT      salary    FROM      managers  );

It returns one row:

id | first_name | last_name |  salary----+------------+-----------+----------  2 | Charlie    | Davis     | 55000.00(1 row)

How it works.

First, execute the subquery in theANY operator that returns the salary of managers:

SELECT salary FROM managers;

Output:

salary---------- 60000.00 55000.00 58000.00(3 rows)

Second, compare the salary of each row in theemployees table with the values returned by the subquery and include the row that has a salary equal to the one in the set (60K,55K, and58K).

2) Using ANY operator with > operator example

The following example uses theANY operator to find employees who have salaries greater than the manager’s salaries:

SELECT  *FROM  employeesWHERE  salary > ANY (    SELECT      salary    FROM      managers  );

Output:

id | first_name | last_name |  salary----+------------+-----------+----------  9 | Jack       | Anderson  | 56000.00 11 | Liam       | Clark     | 59000.00(2 rows)

The output indicates that the two employees have a higher salary than the manager’s.

  • Jack has a salary of 56K which is greater than 55K.
  • Liam has a salary of 59K which is greater than 55K and 58K.

3) Using ANY operator with < operator example

The following example uses theANY operator to find employees who have salaries less than the manager’s salaries:

SELECT  *FROM  employeesWHERE  salary < ANY (    SELECT      salary    FROM      managers  );

Output:

id | first_name | last_name |  salary----+------------+-----------+----------  1 | Bob        | Williams  | 45000.00  2 | Charlie    | Davis     | 55000.00  3 | David      | Jones     | 50000.00  4 | Emma       | Brown     | 48000.00  5 | Frank      | Miller    | 52000.00  6 | Grace      | Wilson    | 49000.00  7 | Harry      | Taylor    | 53000.00  8 | Ivy        | Moore     | 47000.00  9 | Jack       | Anderson  | 56000.00 10 | Kate       | Hill      | 44000.00 12 | Mia        | Parker    | 42000.00(11 rows)

It returns all the rows with theemployee type because they have a value in thesalary column less than any value in the set (55K, 58K, and 60K).

Summary

  • Use the PostgreSQLANY operator to compare a value to a set of values returned by a subquery.

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp