{"id":39,"date":"2009-12-17T02:37:24","date_gmt":"2009-12-17T07:37:24","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=39"},"modified":"2011-09-26T21:50:34","modified_gmt":"2011-09-27T02:50:34","slug":"correlation_names_and_column_name_-qualifiers_in_correlated_references","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/","title":{"rendered":"Correlation names and Column name qualifiers in correlated references"},"content":{"rendered":"

A correlation name<\/strong><\/em> can be defined in the from clause of a query and in the first clause of an update or delete statement.<\/p>\n

for example, the clause FROM X.MYTABLE Z establishes Z as a correlation name for X.MYTABLE.<\/p>\n

With Z defined as correlation name of X.MYTABLE, only Z can be used to qualify reference to a column of that instance of X.MYTABLE in that SELECT statement.<\/p>\n

If a correlation name is specified for a table, view, nickname or alias name, any qualified reference to a column of that table, view, nickname or alias must use the correlation name, rather than\u00a0 the table, view, nickname or alias name.<\/p>\n

Example<\/p>\n


\nSELECT<\/span> *<\/span>
\nFROM<\/span> employee<\/span> e<\/span>
\nWHERE<\/span> employee<\/span>.<\/span>project<\/span> =<\/span> \u2019abc\u2019<\/span> \/*\u00a0incorrect\u00a0*\/<\/span>
\n<\/span><\/code><\/p>\n

The qualified reference to PROJECT should instead use the correlation name, \u2033E\u2033, as shown below:
\n
\nSELECT<\/span> *<\/span>
\nFROM<\/span> employee<\/span> e<\/span>
\nWHERE<\/span> e<\/span>.<\/span>project<\/span> =<\/span> \u2019abc\u2019<\/span><\/span><\/code><\/p>\n

below is also valid, there is no qualified reference to PROJECT.<\/p>\n

SELECT<\/span> *<\/span>
\nFROM<\/span> employee<\/span> e<\/span>
\nWHERE<\/span> project<\/span> =<\/span> \u2019abc\u2019<\/span><\/span><\/code><\/p>\n

A fullselect is a form of a query that may be used as a component of various SQL statement.
\nA fullselect used within a search condition of any statement is called a subquery<\/strong><\/em>.
\nA fullselect used to retrieve a single value as an expression within a statement is called a scalar fullselect<\/strong><\/em> or scalar subquery<\/strong><\/em>.
\nA fullselect used in the FROM clause of a query is called a nest table expression<\/strong><\/em>.<\/p>\n

Subqueries in search conditions, scalar subqueries and nested table expression are referred to as subqueries through the remainder of this topic.<\/p>\n

A subquery may includes subqueries of its own, and these may, in turn, include subqueries. Thus an SQL statement may contain a hierarchy of subqueries. Those elements of the hierarchy that contain subqueries are said to be at a higher level than the subqueries they contain.<\/p>\n

Every element of the hierarchy contains one or more table designators. A subquery can reference not only the columns of the tables at its own level in the hierarchy, but also the columns of the tables identified previously in the hierarchy, back to the highest level of the hierarchy. A reference to a column of a table identified at a higher level is called a correlated reference.<\/p>\n

For compatibility with existing standards for SQL, both qualified and unqualified column names are allowed as correlated reference. However, it is good practice to qualify all column references used in subqueries.<\/p>\n

When a column name in a subquery is qualified, each level of hierarchy is searched, starting at the same query as the qualified column name appears and continuing to the higher levels of hierarchy until a table designator that matches the qualifier is found. Once found, it is verified that the table contains the given column. If the table is found at a higher level than the level containing column name,\u00a0 then it is a correlated reference to the level where the table designator was found.<\/p>\n","protected":false},"excerpt":{"rendered":"

A correlation name can be defined in the from clause of a query and in the first clause of an update or delete statement. for example, the clause FROM X.MYTABLE Z establishes Z as a correlation name for X.MYTABLE. With Z defined as correlation name of X.MYTABLE, only Z can be used to qualify reference to a column of that instance of X.MYTABLE in that SELECT statement. If a correlation name is specified for a table, view, nickname or alias name, any qualified reference to a column of that table, view, nickname or alias must use the correlation name, rather\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16,25],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nCorrelation names and Column name qualifiers in correlated references<\/title>\n<meta name=\"description\" content=\"Correlation names and Column name qualifiers in correlated references\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Correlation names and Column name qualifiers in correlated references\" \/>\n<meta property=\"og:description\" content=\"Correlation names and Column name qualifiers in correlated references\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2009-12-17T07:37:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-09-27T02:50:34+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\",\"name\":\"Correlation names and Column name qualifiers in correlated references\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2009-12-17T07:37:24+00:00\",\"dateModified\":\"2011-09-27T02:50:34+00:00\",\"description\":\"Correlation names and Column name qualifiers in correlated references\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Correlation names and Column name qualifiers in correlated references\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\"},\"headline\":\"Correlation names and Column name qualifiers in correlated references\",\"datePublished\":\"2009-12-17T07:37:24+00:00\",\"dateModified\":\"2011-09-27T02:50:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/\"},\"wordCount\":470,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"articleSection\":[\"reference\",\"sql\"],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/www.dpriver.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Correlation names and Column name qualifiers in correlated references","description":"Correlation names and Column name qualifiers in correlated references","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/","og_locale":"en_US","og_type":"article","og_title":"Correlation names and Column name qualifiers in correlated references","og_description":"Correlation names and Column name qualifiers in correlated references","og_url":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/","og_site_name":"SQL and Data Blog","article_published_time":"2009-12-17T07:37:24+00:00","article_modified_time":"2011-09-27T02:50:34+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/","url":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/","name":"Correlation names and Column name qualifiers in correlated references","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2009-12-17T07:37:24+00:00","dateModified":"2011-09-27T02:50:34+00:00","description":"Correlation names and Column name qualifiers in correlated references","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Correlation names and Column name qualifiers in correlated references"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/"},"author":{"name":"admin","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7"},"headline":"Correlation names and Column name qualifiers in correlated references","datePublished":"2009-12-17T07:37:24+00:00","dateModified":"2011-09-27T02:50:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/2009\/12\/correlation_names_and_column_name_-qualifiers_in_correlated_references\/"},"wordCount":470,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"articleSection":["reference","sql"],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/www.dpriver.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/39"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=39"}],"version-history":[{"count":15,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/39\/revisions"}],"predecessor-version":[{"id":1019,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/39\/revisions\/1019"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}