Editing Object–relational database (section)

== Comparison to RDBMS ==
An RDBMS might commonly involve [[SQL]] statements such as these:
<syntaxhighlight lang="mysql">
   CREATE TABLE Customers  (
       Id          CHAR(12)    NOT NULL PRIMARY KEY,
       Surname     VARCHAR(32) NOT NULL,
       FirstName   VARCHAR(32) NOT NULL,
       DOB         DATE        NOT NULL   # DOB: Date of Birth
    );
    SELECT InitCap(C.Surname) || ', ' || InitCap(C.FirstName)
      FROM Customers C
     WHERE Month(C.DOB) = Month(getdate())
       AND Day(C.DOB) = Day(getdate())
</syntaxhighlight>
Most {{As of|2007|alt=current}} SQL databases allow the crafting of custom [[function (computer science)|function]]s, which would allow the query to appear as:
<syntaxhighlight lang="mysql">
    SELECT Formal(C.Id)
      FROM Customers C
     WHERE Birthday(C.DOB) = Today()
</syntaxhighlight>
<!-- NOTE: Rather than fix this, only to have it unfixed ''yet'' again, let it stand as an example. The first SQL SELECT statement quoted above is both non-standard, and wrong. InitCap() isn't SQL-92 (or SQL-3), and the query's logic misses Customers born on a leap-day (171,000 United States citizens) for three years out of four. -->

In an object–relational database, one might see something like this, with user-defined data-types and expressions such as <code>BirthDay()</code>:
<syntaxhighlight lang="postgres">
    CREATE TABLE Customers (
      Id           Cust_Id     NOT NULL  PRIMARY KEY,
      Name         PersonName  NOT NULL,
      DOB          DATE        NOT NULL
    );
    SELECT Formal( C.Id )
      FROM Customers C
     WHERE BirthDay ( C.DOB ) = TODAY;
</syntaxhighlight>

The object–relational model can offer another advantage in that the database can make use of the relationships between data to easily collect related records. In an [[address book]] application, an additional table would be added to the ones above to hold zero or more addresses for each customer. Using a traditional RDBMS, collecting information for both the user and their address requires a "join":

<syntaxhighlight lang="mysql">
     SELECT InitCap(C.Surname) || ', ' || InitCap(C.FirstName), A.city
       FROM Customers C JOIN Addresses A ON A.Cust_Id=C.Id -- the join
      WHERE A.city="New York"
</syntaxhighlight>

The same query in an object–relational database appears more simply:

<syntaxhighlight lang="postgres">
    SELECT Formal( C.Name )
      FROM Customers C
     WHERE C.address.city="New York" -- the linkage is 'understood' by the ORDB
</syntaxhighlight>