DEV Community

Anjali Gurjar
Anjali Gurjar

Posted on

SQL(subquery)

Subqueries are a powerful feature in SQL that allow you to use a query result as part of another query. Depending on how and where you use subqueries, they can be categorized into several types. Here are the main types:

  1. Single-Row Subquery Returns only one row of results. Commonly used with comparison operators like =, <, >, <=, >=. Example: sql Copy code SELECT name FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 10);
  2. Multi-Row Subquery Returns multiple rows as results. Used with operators like IN, ANY, ALL. Example: sql Copy code SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
  3. Correlated Subquery Refers to columns in the outer query and executes once for every row processed by the outer query. Commonly used in filtering and calculations. Example: sql Copy code SELECT name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
  4. Nested Subquery A subquery inside another subquery. Example: sql Copy code SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location = 'London'));
  5. Scalar Subquery Returns a single value (a single row and column). Can be used in SELECT, WHERE, or FROM clauses. Example: sql Copy code SELECT name, (SELECT department_name FROM departments WHERE department_id = e.department_id) AS department_name FROM employees e;
  6. Derived Table Subquery A subquery used in the FROM clause, essentially treated as a temporary table. Example: sql Copy code SELECT dept_name, AVG(salary) FROM (SELECT department_id, salary FROM employees WHERE salary > 5000) AS high_salaries JOIN departments ON high_salaries.department_id = departments.department_id GROUP BY dept_name;
  7. Exists/Not Exists Subquery Checks for the existence or non-existence of rows. Commonly used in conditional logic. Example: sql Copy code SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location = 'London');
  8. Inline View Subquery Acts like a view created on-the-fly, usually used in the FROM clause. Example: sql Copy code SELECT avg_salary FROM (SELECT AVG(salary) AS avg_salary FROM employees WHERE department_id = 10); Summary of Key Uses Filters: WHERE, HAVING, EXISTS. Calculations: Nested or scalar subqueries for calculated columns. Joins: Derived tables or inline views for complex joins. Dynamic Conditions: IN, ANY, ALL, and correlated subqueries.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more