{"id":471,"date":"2014-10-07T10:56:00","date_gmt":"2014-10-07T17:56:00","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=471"},"modified":"2016-10-21T13:33:23","modified_gmt":"2016-10-21T20:33:23","slug":"nested-queries","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/databases\/nested-queries\/","title":{"rendered":"Nested Queries"},"content":{"rendered":"

A nested query or subquery is a query within a query. Subqueries are usually added in the WHERE clause of the SQL statement when you know the conditions for a field’s value but do not know the exact value in the database. Conditions can be created using comparison operators such as =, IN, EXIST or UNIQUE.<\/p>\n

Comparing single values from subqueries
\n<\/strong><\/h2>\n

When nested queries return a single value, the returned value can be compared using\u00a0 =, <, >, etc. Example: finding student(s) who got the best grade registered.<\/p>\n

SELECT StudentID, FinalGrade
\nFROM Registrations
\nWHERE FinalGrade =<\/span> (SELECT MAX (FinalGrade)<\/em><\/span> FROM Registrations<\/em><\/span>)<\/em><\/span><\/p>\n

The nested query returns the maximum grade recorded, which is used in the parent query to find the corresponding student.<\/p>\n

IN operator<\/h2>\n

Compares a field to each value in a list returned by a nested query. Example: names of students who have registered in BAMS 580D.<\/p>\n

SELECT StudentName
\nFROM Students
\nWHERE StudentID IN<\/span> (SELECT StudentID\u000b<\/em> FROM Registrations<\/em> WHERE CourseID= ‘BAMS 580D’)<\/em><\/span><\/p>\n

EXISTS operator<\/h2>\n

Returns TRUE if the result set of the subquery is not empty. Example: names of students who have registered in BAMS 580:<\/p>\n

SELECT S.StudentName
\nFROM Students S
\nWHERE EXISTS<\/span> (SELECT *\u000b FROM Registrations R\u000b<\/em><\/span>
\n WHERE R.CourseID= \u2018BAMS 580\u2019 AND S.StudentID = R.StudentID)<\/em><\/span><\/p>\n

The subquery is recomputed for each Student tuple of the first query so the fields from the parent query can be used in the nested query.<\/p>\n

Other subquery operators<\/h2>\n

Other operators to compare results from nested queries are:<\/p>\n\n\n\n\n\n
UNIQUE<\/td>\nChecks for duplicate tuples in a subquery and returns true if there are no duplicates.<\/td>\n<\/tr>\n
ALL<\/td>\nCompares a value with every value in a list or returned by a query.
\nMust be preceded by =, >, <, etc. Evaluates to TRUE if the query returns no rows.<\/td>\n<\/tr>\n
ANY<\/td>\nCompares a value to each value in a list or returned by a query.
\nMust be preceded by =, >, <, etc. Evaluates to FALSE if the query returns no rows.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Subqueries in FROM clause<\/h2>\n

Used in the FROM clause, nested queries are an alternate way of pre-processing data from tables, for instance to filter students from 2014:<\/p>\n

SELECT Students2014.*
\nFROM (SELECT StudentID FROM Students WHERE Year = 2014) as Students2014<\/p>\n

WITH clause<\/h2>\n

Another way of creating nested queries is using the WITH clause<\/a> (not available in MS Access), which creates a temporary table that can be queried.<\/p>\n

See SQL subquery<\/a> to read more about subqueries. See Oracle SQL Operators<\/a> to read more about operators.<\/p>\n","protected":false},"excerpt":{"rendered":"

A nested query or subquery is a query within a query. Subqueries are usually added in the WHERE clause of the SQL statement when you know the conditions for a field’s value but do not know the exact value in the database. Conditions can be created using comparison operators such as =, IN, EXIST or […]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":74,"menu_order":5,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-471","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/471","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=471"}],"version-history":[{"count":13,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/471\/revisions"}],"predecessor-version":[{"id":1541,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/471\/revisions\/1541"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/74"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=471"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}