SQL VALUE

What is the SQL VALUE operator?

VALUE returns the entire object (row value) referenced by a table alias or REF, often used with object-relational tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL VALUE: Oracle (full support), IBM Db2 (object extensions), SQL Standard (1999+). Not available in MySQL, PostgreSQL, SQL Server, or SQLite.

SQL VALUE Full Explanation

VALUE is an SQL operator defined in the SQL-1999 standard and implemented in object-relational databases such as Oracle. It takes a table alias (usually of an object table) or a REF column and returns the complete object instance stored in that row. VALUE is most useful when a table stores objects of a user-defined type and you need the whole object rather than individual attributes. In pure relational tables, selecting * already gives all column values, so VALUE is rarely needed.Key points:- Works only on tables whose rows are of an object type, or on REF columns that point to such rows.- Returns a single typed object, not a scalar.- Can be used in SELECT, WHERE, GROUP BY, and ORDER BY clauses.- Different from the more common VALUES row constructor and from MySQL’s deprecated VALUES() function.- Not supported in most purely relational engines such as MySQL, SQLite, or PostgreSQL without object extensions.

SQL VALUE Syntax

SELECT VALUE(table_alias)
FROM   object_table AS table_alias [WHERE ...];

SQL VALUE Parameters

Example Queries Using SQL VALUE

-- Create a simple object type (Oracle syntax)
CREATE TYPE person_t AS OBJECT (
  id         NUMBER,
  first_name VARCHAR2(50),
  last_name  VARCHAR2(50)
);
/

-- Create an object table of that type
CREATE TABLE person OF person_t;

-- Insert sample data
INSERT INTO person VALUES (person_t(1,'Ada','Lovelace'));
INSERT INTO person VALUES (person_t(2,'Alan','Turing'));

-- Retrieve full objects
SELECT VALUE(p) AS person_object
FROM   person p;

-- Use VALUE on a REF column
CREATE TABLE employee (
  emp_id   NUMBER PRIMARY KEY,
  person_r REF person_t
);
INSERT INTO employee VALUES (1, (SELECT REF(p) FROM person p WHERE id = 1));

SELECT VALUE(e.person_r) AS person_object
FROM   employee e;

Expected Output Using SQL VALUE

Each row returns a single person_t object containing all attribute values, which client libraries can treat as a structured object rather than separate columns

Use Cases with SQL VALUE

Fetch whole objects from an object table for manipulation in PL/SQL, JDBC, or ORM frameworks.
Compare two object instances directly in WHERE clauses.
Dereference a REF column to obtain the underlying object.

Common Mistakes with SQL VALUE

Confusing VALUE with the VALUES row constructor used in INSERT statements.
Trying to use VALUE on regular relational tables that are not defined as object tables.
Expecting VALUE to be available in MySQL or PostgreSQL without object extensions.

First Introduced In

SQL:1999 object-relational extensions (Oracle 8i)

Frequently Asked Questions

What is the difference between VALUE and SELECT *?

VALUE returns the row as one typed object, while SELECT * returns individual scalar columns. Use VALUE when working with object tables.

Can I update the object returned by VALUE?

Yes. In PL/SQL you can fetch the object into a variable, modify its attributes, and then update the table by assigning the variable back to VALUE(table_alias).

Does VALUE work with nested tables or arrays?

You can use VALUE on the outer object. To access nested collections, use collection methods (e.g., TABLE(), CAST()) after retrieving the object.

Why do I get "invalid table alias" errors?

VALUE takes a table alias, not the table name itself. Always supply the alias used in the FROM clause, e.g., VALUE(p) not VALUE(person).