arcgis desktop - Select rows where column values don't match the domain

In ArcGIS Desktop, I can use the Validate Features option in the Editor Toolbar menu to validate feature class columns against their respective domains. More info here in the Validating Features section.

However, this functionality only works on spatial feature classes, not on non-spatial tables. Additionally, even if I were using a spatial feature-class, the feedback that the Validate Features function provides is too limited to be useful.

I want to select records in non-spatial tables where column values don't match their respective domains.

How can I do this?

Answer

Sample data:

USER1.A_NUMBERS_TABLE 
+--------------+

| NUMBER_FIELD |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
|           66 |
+--------------+

---

A_NUMBERS_DOMAIN_VW:

SELECT
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Code'),1,255) AS Code,
    SUBSTR(EXTRACTVALUE(CodedValues.COLUMN_VALUE, 'CodedValue/Name'),1,255) AS Description
FROM
    SDE.GDB_ITEMS_VW I 
JOIN 
    SDE.GDB_ITEMTYPES IT 

    ON I.Type = IT.UUID, TABLE(XMLSEQUENCE(XMLType(Definition).Extract('/GPCodedValueDomain2/CodedValues/CodedValue'))) CodedValues 
WHERE
    I.NAME = 'A_NUMBERS_DOMAIN'


+------+-------------+
| CODE | DESCRIPTION |
+------+-------------+
|    1 | ONE         |
|    2 | TWO         |

|    3 | THREE       |
|    4 | FOUR        |
|    5 | FIVE        |
+------+-------------+

Solution:

Use a sub-query.

In the Select by Attributes window:

NUMBER_FIELD NOT IN (
    SELECT 

        CODE 
    FROM 
        USER1.A_NUMBERS_DOMAIN_VW)

Where I got the idea:

I often use the sub-query from the Query for duplicate records in a feature class table example:

In the Select by Attributes window:

NUMBER_FIELD In (
    SELECT 
        NUMBER_FIELD 

    FROM 
        USER1.A_NUMBERS_TABLE 
    GROUP BY 
        NUMBER_FIELD 
    HAVING Count(*)>1 )

This is a pretty clever way to execute SQL in the Select by Attributes window, without being able to execute full SQL statements.

However, this only works in SQL-based databases such as enterprise geodatabases and personal geodatabases (not file geodatabases) -- as per the documentation. And I don't think it would work between different databases (query from one database to another).

---

^{Note: In the Select by Attributes window, we are limited to using only the WHERE clause (ESRI often calls this an SQL expression). Using sub-queries in the Select by Attributes window like this is extremely inefficient; sub-queries are really just a last resort. It would be much more appealing to use a join, rather than a sub-query, but we can't do joins because we can't execute full SQL statements from the Select by Attributes window.}