in this tutorial we learn to all about subquery Examples like as a Subqueries with the SELECT Statement, INSERT Statement, UPDATE Statement, DELETE Statement, WHERE clause, SELECT clause, FROM clause, Subquery with EXISTS, Subquery with NOT EXISTS, Nested Subqueries, Subqueries within Other Statements, Derived Tables or many more.
Also Read: Nested Queries Subqueries in SQL Example
MySQL & SQL Subquery
A subquery is a SQL query within a query. They are nested queries that provide data to the enclosing query. Subqueries can return individual values or a list of records. Note that subquery statements are enclosed between parenthesis.
Subqueries with the SELECT Statement
SQL> SELECT * FROM WORKERS WHERE ID IN (SELECT ID FROM WORKERS WHERE SALARY > 4500) ;
Subqueries with the INSERT Statement
SQL> INSERT INTO WORKERS_BKP SELECT * FROM WORKERS WHERE ID IN (SELECT ID FROM WORKERS) ;
Subqueries with the UPDATE Statement
SQL> UPDATE WORKERS SET SALARY = SALARY * 0.25 WHERE AGE IN (SELECT AGE FROM WORKERS_BKP WHERE AGE >= 27 );
Subqueries with the DELETE Statement
SQL> DELETE FROM WORKERS WHERE AGE IN (SELECT AGE FROM WORKERS_BKP WHERE AGE >= 27 );
SELECT p.item_id, p.item_name FROM items p WHERE p.item_id IN (SELECT inv.item_id FROM inventory inv WHERE inv.quantity > 10);
SELECT distributors.distributor_name, subquery1.total_amt FROM distributors, (SELECT distributor_id, SUM(books.amount) AS total_amt FROM books GROUP BY distributor_id) subquery1 WHERE subquery1.distributor_id = distributors.distributor_id;
SELECT e1.sirnm, e1.profilenm, (SELECT MAX(salary) FROM workers e2 WHERE e1.worker_id = e2.worker_id) subquery2 FROM workers e1;
Subquery with EXISTS
SELECT * FROM mobilerequest bo WHERE EXISTS( SELECT * FROM longshops l WHERE bo.MobileNumber = l.MobileNumber )
Subquery with NOT EXISTS
SELECT * FROM mobilerequest bo WHERE NOT EXISTS( SELECT * FROM longshops l WHERE bo.MobileNumber = l.MobileNumber )
SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor WHERE film_id = (SELECT film_id FROM film WHERE title = 'Ace Goldfinger') );
Subqueries within Other Statements
INSERT INTO ace_goldfinger_actors (profilenm, sirnm) SELECT profilenm, sirnm FROM actor a INNER JOIN film_actor fa ON a.actor_id = fa.actor_id WHERE fa.film_id = (SELECT film_id FROM film WHERE title = 'Ace Goldfinger');
SELECT AVG(a) FROM (SELECT buyer_id, SUM(amount) a FROM payment GROUP BY buyer_id) AS totals;
I hope you get an idea about subquery 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.