sql subquery multiple tables Example

in this tutorial we learn to all about subquery Examples like as a MySQL Subquery, MySQL subquerys in WHERE clause, IN and NOT IN operators, FROM clause, MySQL correlated subquery, EXISTS and NOT EXISTS, Row Sub-Query, Predicate Sub-queries, Quantified Subqueries or many more.

Also Read: Nested Queries Subqueries in SQL Example

SQL Subquery, IN SELECT – with Examples

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

MySQL Subquery

SELECT 
    lastName, firstName
FROM
    workers
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

MySQL subquerys in WHERE clause

SELECT 
    buyerNumber, 
    checkNumber, 
    amount
FROM
    paypals
WHERE
    amount = (SELECT MAX(amount) FROM paypals);

MySQL subquerys with IN and NOT IN operators

SELECT 
    buyerName
FROM
    buyers
WHERE
    buyerNumber NOT IN (SELECT DISTINCT
            buyerNumber
        FROM
            books);

MySQL subquerys in the FROM clause

SELECT 
    MAX(items), 
    MIN(items), 
    FLOOR(AVG(items))
FROM
    (SELECT 
        requestNumber, COUNT(requestNumber) AS items
    FROM
        requestdetails
    GROUP BY requestNumber) AS lineitems;

MySQL correlated subquerys

SELECT 
    requestNumber, 
    COUNT(requestNumber) AS items
FROM
    requestdetails
GROUP BY requestNumber;

MySQL subquerys with EXISTS and NOT EXISTS

SELECT 
    requestNumber, 
    SUM(amountEach * quantityOrdered) total
FROM
    requestdetails
        INNER JOIN
    books USING (requestNumber)
GROUP BY requestNumber
HAVING SUM(amountEach * quantityOrdered) > 60000;

using the EXISTS operator:

SELECT 
    buyerNumber, 
    buyerName
FROM
    buyers
WHERE
    EXISTS( SELECT 
            requestNumber, SUM(amountEach * quantityOrdered)
        FROM
            requestdetails
                INNER JOIN
            books USING (requestNumber)
        WHERE
            buyerNumber = buyers.buyerNumber
        GROUP BY requestNumber
        HAVING SUM(amountEach * quantityOrdered) > 60000);

Row Sub-Query

Select full_names From clients WHERE clientship_number = (SELECT clientship_number FROM paypals WHERE amount_paid = (SELECT MAX(amount_paid) FROM paypals));

SUBQUERY DataBase Structure

CREATE DATABASE VATSA_SUBQUERY;  
  
USE VATSA_SUBQUERY;  
  
CREATE TABLE Library(  
  MobileNumber int NOT NULL,  
  MobileCode varchar(15) NOT NULL,  
  MobileIssue int NOT NULL,  
  CostEach decimal(10, 2) NOT NULL,  
  PRIMARY KEY(MobileNumber, MobileCode)  
);  
  
CREATE TABLE Mobilerequest(  
  MobileNumber int NOT NULL,  
  requestDate date NOT NULL,  
  shippedDate date DEFAULT NULL,  
  Status varchar(50),  
  PRIMARY KEY(MobileNumber)  
); 

Predicate Sub-queries

IN Subquery

SELECT * FROM mobilerequest WHERE MobileNumber IN(  
  SELECT mobilenumber FROM longshops  
) 

NOT IN Subquery

SELECT * FROM mobilerequest WHERE MobileNumber NOT IN(  
  SELECT mobilenumber FROM longshops  
) 

Quantified Subqueries

SELECT * FROM mobilerequest WHERE MobileNumber = ANY(  
  SELECT mobilenumber FROM longshops  
) 

I hope you get an idea about how to write subquery in sql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.