Subqueries


A SELECT statement form appearing within another SELECT statement is known as a subquery. A subquery can appear in the search-condition of a WHERE clause for another SELECT statement.

The IN Predicate
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.

SELECT [ ALL | DISTINCT ] expression-1 {, expression-2 }
  FROM table-name-1 [ corr-name-1 ] {, table-name-2 [ corr-name-2 ] }
  [ WHERE condition-1 ]
  [ GROUP BY column-name-1, {, column-name-2 } ]
  [ HAVING condition-2 ];

Condition: expr [NOT] IN (subquery) | expr [NOT] IN ( val {, val ...} )

Question VIII (The IN Predicate)
Get cid values of customers who place orders with agents in Duluth or Dallas.

The query could be done in two steps:
  1. Question VIII.a: Find all agents based in Duluth or Dallas.  
agent
aid aname city percent
a01 Smith New York 6
a02 Jones Newark 6
a03 Brown Tokyo 7
a04 Gray New York 6
a05 Otasi Duluth 5
a06 Smith Dallas 5





aid
a05
a06

(a)
There are many other SQL operators like For a complete list, check SQL operators.
  1. Question VIII.b: The above result can be used to represent a set of values.  
order3
orderno month cid aid pid qty dollar
1011 jan c001 a01 p01 1000 450.00
1012 jan c001 a01 p01 1000 450.00
1019 feb c001 a02 p02 400 180.00
1017 feb c001 a06 p03 600 540.00
1018 feb c001 a03 p04 600 540.00
1023 mar c001 a04 p05 500 450.00
1022 mar c001 a05 p06 400 720.00
1025 apr c001 a05 p07 800 720.00
1013 jan c002 a03 p03 1000 880.00
1026 may c002 a05 p03 800 704.00
1015 jan c003 a03 p05 1200 1104.00
1014 jan c003 a03 p05 1200 1104.00
1021 feb c004 a06 p01 1000 460.00
1016 jan c006 a01 p01 1000 500.00
1020 feb c006 a03 p07 600 600.00
1024 mar c006 a06 p01 800 400.00

aid
a05
a06

(a)

cid
c001
c002
c004
c006

(b)


Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

SQL Statement:

Edit the SQL statement and click     to see the result, or  

Result:
The Database includes:
The Database includes:

TablenameRecord
Customers91
Categories8
Employees10
OrderDetails518
Orders196
Products77
Shippers3
Suppliers29




      As I have grown older,    
      I have learned that pleasing everyone is impossible,    
      but pissing everyone off is a piece of cake.