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.
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);
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) );
SELECT * FROM mobilerequest WHERE MobileNumber IN( SELECT mobilenumber FROM longshops )
NOT IN Subquery
SELECT * FROM mobilerequest WHERE MobileNumber NOT IN( SELECT mobilenumber FROM longshops )
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.