Using Subquery in WHERE- Subquery in FROM

DML Statements(Subqueries)

A) Using Subquery in WHERE Clause

sql code

SELECT <column list>
FROM <table names>
WHERE expression operator 
					( SELECT <column list>
                                             FROM <table names>)

Example 1:

Get the names of the customers who have ordered at least one order.

sql code

SELECT  Customers.ContactName
FROM Customers
WHERE Customers.CustomerID IN 
   (
    SELECT Orders.CustomerID
    FROM Orders
   );

Example 2:

Get the supplier names of the suppliers who supply category number 3.
sql code

SELECT Suppliers.ContactName
FROM Suppliers
WHERE  Suppliers.SupplierID IN
  (
	SELECT Products.SupplierID
	FROM Products
	WHERE Products.CategoryID=3
  );

Example 3:

Get the books titles priced higher than the lowest priced book that has a type 'trad_cook'.


sql code

SELECT Title
FROM Titles
WHERE Price >
 (
   SELECT MIN(Price)
   FROM Titles
   WHERE Type = 'trad_cook'
);

B) Using Subquery in FROM Clause "Represents a table"

sql code

SELECT <column list>
FROM <table names>, 	    ( SELECT <column list>
                                        FROM <table names>)

[WHERE <condition>]

Example:

Get the names of all customers and employees living in USA , but with customers not working as Sales Agents.

sql code

SELECT C. ContactName "Cust Name", E.FirstName + ' ' + E.LastName "Emp Name" 
FROM Customers C , 
		 	 (
                   		   SELECT FirstName,LastName
                   		   FROM Employees
                                   WHERE Country = 'USA'
                                 ) E
WHERE C.Country = 'USA'
AND C.ContactTitle <> 'Sales Agent';

C) Using Subquery in SELECT Clause "Represents column(s)"

sql code

SELECT <column list> , ( SELECT <column list>
                                     FROM <table names>)

FROM <table names>	   
[WHERE <condition>]

Example:

Get the name , job (title) and number of orderes requested by each customer.

sql code

SELECT ContactName ,ContactTitle , (SELECT COUNT(*) 
                                                               FROM Orders 
                                                               WHERE Customers.CustomerId = Orders.CustomerId  
 				               ) AS "Number of Orders"		
FROM Customers;

Note:

When using subquery in the where clause , take care when using operators ;
If the subquery returns more than one value you can't use operator such as = , but you may use the IN operator.