SubQuery or Embedded SELECT

A subquery is basically a query within a query
Also known as an embedded select.
A Subselect is a SELECT which works in conjunction with another SELECT. A nested SEL.ECT is a kind of subselect where the inner select passes to the where criteria for the outer SELECT

You can add subqueries in the WHERE clause pf another SQL statement to obtain values based on an unknown conditional value. Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second.

EXISTS - Tests the existence of at least one row that satifies the selection criteria in a subquery. If the subquery returns at least one row then it is TRUE otherwise FALSE
Subqueries can be very useful when you need to select rows from a table with a condition that depends on the data in the tabel itself.

If you have any embedded SELECT statements any tables / columns you refer to in the inner SELECT must be declared under the "FROM"

You can place a subquery in any of the three clauses WHERE , HAVING and FROM

Types of Subquery

Single Row - queries that return only a single row from the inner select statement
Multiple Row - queries that return more than one row from the inner select statement
Multiple Column - queries that return more than one columns from the inner select statement

Use single row operators with single row subqueries and use multiple row operators with multiple row subqueries
A subquery must always be enclosed in parentheses
A common error with subqueries is more than one row or no rows is returned from a single-row subquery

You can display data from a main query by using a group function in a subquery to return a single row

Sub Queries

Also known as embedded selects, inner query or nested query
A subquery is enclosed in parentheses

SELECT  last_name, 
        sales 
FROM    customer 
WHERE   state_cd = ( SELECT MAX(state_cd) FROM state ) 

SELECT  S."myrowno", 
        S.ColumnName 
FROM    (SELECT rowno "myrowno" 
                ,ColumnName 
                FROM View1 
                WHERE rowno < 4) S 
WHERE   S."myrowno" = 3 

Running SQL statements with embedded subqueries can affect performance. You may need to work closely with your DBA to optimise statements with subquery processing.

Using subqueries allow you to write SQL statements that can stay the same when your data doesn't
Non pairwise subquery

Single row functions can be nested at any level

Multiple row operators

ALL - compare value to every value returned by the subquery
IN - equal to any member in a list
ANY - compare value to each value returned by the subquery

=--this only works in Access as the ampersand character is not recognised in SQL Server
--The string concatenation character in SQL Server is +
SELECT CompanyName & '-' & ContactName
FROM CUSTOMERS

=--this works in both SQL Server and Access
SELECT CompanyName + '-' + ContactName
FROM CUSTOMERS
=--the only difference is when the COLUMN contains a Null value
=--Null + string = string
=--Null & string = Null