SQL Subquery, IN SELECT – with Examples

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 );

WHERE clause

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);

FROM clause

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 clause

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  
) 

Nested Subqueries

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');

Derived Tables

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.