subquery in sql

Today, We want to share with you subquery in sql.In this post we will show you subquery, hear for how to join two tables using subquery in sql we will give you demo and example for implement.In this post, we will learn about sql subquery multiple tables with an example.

Types of subquery in sql

in this tutorial we learn to all about subquery Examples like as a Sample Queries, the WHERE clause, Scalar Subqueries, Multiple-Row Subqueries, Correlated Subqueries, inner query depends on the outer query, WHERE statement or many more.

Also Read: Nested Queries Subqueries in SQL Example

Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery.

Sample Queries

Select NAME, LOCATION, PHONE_NUMBER from DATABASE 
WHERE ROLL_NO IN
(SELECT ROLL_NO from STUDENT where SECTION=’A’); 

include it in the WHERE clause

SELECT *
FROM vendors_agents
WHERE agency_fee > 
(SELECT AVG(agency_fee)
 FROM vendors_agents);

Scalar Subqueries

SELECT name AS painting,
     amount,
     (SELECT AVG(amount)
  FROM paintings) AS avg_amount
FROM paintings;

Multiple-Row Subqueries

SELECT AVG(agency_fee)
FROM vendors_agents
WHERE id NOT IN (SELECT id
                 FROM managers);

Correlated Subqueries

SELECT city, 
 (SELECT count(*)
  FROM paintings p
  WHERE g.id = p.gallery_id) total_paintings
FROM galleries g;

the inner query depends on the outer query

SELECT g.city, count(p.name) AS total_paintings
FROM galleries g
JOIN paintings p
ON g.id = p.gallery_id
GROUP BY g.city;

WHERE statement

SELECT sirnm, 
       profilenm,
       agency_fee
FROM vendors_agents sa1
WHERE sa1.agency_fee >= (SELECT avg(agency_fee)
                         FROM vendors_agents sa2 
                         WHERE sa2.gallery_id = sa1.gallery_id);

I hope you get an idea about 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.