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.

Read Also:  subquery in sql

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.

Read Also:  How to convert php array to json object with example?

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  SQL Subquery, IN SELECT - with Examples
  2. Read Also:  how to disable browser back button in php?
  3. Read Also:  How to get nth-child Selector using Jquery
  4. Read Also:  install node on windows
  5. Read Also:  Simple VueJS Array Mutate Example with Demo
  6. Read Also:  How to show selected value from database in dropdown using Laravel?
  7. Read Also:  SQL SELECT DISTINCT Statement
  8. Read Also:  sql multiple subqueries in (select statement)
  9. Read Also:  sql subquery example
  10. Read Also:  sql subquery examples
CLOSEX