Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL: ANY/SOME Operator

PostgreSQL supports ANY and SOME operators that compare a column value or literal value with the result of a subquery that returns a single-column value.

ANY and SOME are the same. You can use any one.

  • A subquery used with the ANY or SOME operator, can only return a single column values.
  • The ANY or SOME operator must be preceded by comparison operators like =, !=, >, >=, <, <=.
  • The ANY or SOME operator uses AND with the result values of a subquery to compare a column of the outer query.
  • The data type of the returned values from a subquery must be the same data type as the outer query expression.
Syntax
expression <operator> ANY( subquery );

--or

expression <operator> SOME( subquery );

-- or    

SELECT * FROM <table_name>
WHERE <column_name> <operator> ANY( subquery );

SELECT * FROM table_name
WHERE <column_name> <operator> SOME( subquery )

Let's use the following Department (parent table) and Employee (child table) to demonstrate the ANY operator.

Let's find out list of Employees who belongs to 'HR' or 'IT' department. We can do so by running below subquery with ANY operator.

Example: ANY Operator
SELECT * FROM Employee
WHERE dept_id = ANY (SELECT dept_id FROM Department
					WHERE dept_name IN ('HR', 'IT') );

In the above query, a subquery SELECT dept_id FROM Department WHERE dept_name IN ('HR', 'IT') will be executed first, and it will return dept_id values 1,2. Now, the ANY or SOME operator would use OR operator with all the return values and form the query as shown below.

SELECT * FROM Employee
WHERE dept_id = 1 OR dept_id = 2;

When you use the = operator with ANY, it works same as the IN operator.

Note that the following query with the SOME operator would return the same result as above.

Example: SOME Operator
SELECT * FROM Employee
WHERE dept_id = SOME (SELECT dept_id FROM Department
					WHERE dept_name IN ('HR', 'IT'));
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.