Home » subquery in sql

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.

Read Also:  sql subquery multiple tables Example

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

Read Also:  sql subquery example

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.

Read Also:  how to write subquery in sql

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  pagination code in php with next and previous demo
  2. Read Also:  sql subquery examples
  3. Read Also:  Codeigniter File Download Helper - Download file from database - URL using Server
  4. Read Also:  How to String Replace in PHP Functions?
  5. Read Also:  Auto Post on Facebook page with php using Graph Api
  6. Read Also:  sql multiple subqueries in (select statement)
  7. Read Also:  How to use Datatables in Laravel 5.8
  8. Read Also:  How to disable/turn off specific css in wordpress?
  9. Read Also:  check if folder exists before create directory Laravel
  10. Read Also:  Toggle Switches Laravel, Ajax and jQuery example

Leave a Comment

Your email address will not be published. Required fields are marked *