DEV Community

Ricardo Tovar
Ricardo Tovar

Posted on

Subqueries

Subqueries are queries that come within another larger query. They are useful to obtain specific information that will later be used in the main query.

Type

  • Single row subquery:

Return a single value.

  • Multirow subquery

Return multiple rows

  • Correlated subquery

It depends on a column in the main query, meaning it is executed once for each row in the outer query.

  • Nested subquery

A subquery that contains another subquery within it.

  • Scalar subquery

Returns a single value(similar to single row subquery) but can be used as a column in the SELECT.

A subquery can appear in 3 places of your query:

  • SELECT
  • FROM OR INNER
  • WHERE

And each one of these parts have a set of rules .
For example :

  • A subquery in SELECT should return a single value like:
SELECT 
    nombre,
    (SELECT MAX(salary) FROM Employees) AS max_salary
FROM Employees;
Enter fullscreen mode Exit fullscreen mode
  • A subquery in FROM OR INNER should return a set of rows

  • A subquery in WHERE should return a column with many rows like:

SELECT NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = (SELECT ID FROM DEPARTMENTS WHERE DEPARTMENT = 'IT');
Enter fullscreen mode Exit fullscreen mode

Billboard image

Imagine monitoring that's actually built for developers

Join Vercel, CrowdStrike, and thousands of other teams that trust Checkly to streamline monitor creation and configuration with Monitoring as Code.

Start Monitoring

Top comments (0)

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay