{"id":3963,"date":"2025-06-07T08:23:24","date_gmt":"2025-06-07T08:23:24","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=3963"},"modified":"2025-06-07T10:00:01","modified_gmt":"2025-06-07T10:00:01","slug":"sql-subqueries","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/sql\/sql-subqueries\/","title":{"rendered":"SQL Subqueries"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">A <strong>subquery<\/strong> is a query <strong>inside another query<\/strong>. It\u2019s used to fetch intermediate results for comparison, filtering, or transformation.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Types of subqueries:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Scalar Subquery<\/strong> \u2192 returns a single value<\/li>\n\n\n\n<li><strong>Row Subquery<\/strong> \u2192 returns a single row<\/li>\n\n\n\n<li><strong>Table Subquery<\/strong> \u2192 returns a full result set (multiple rows &amp; columns)<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Scalar Subquery \u2013 One Value<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Used where a <strong>single value<\/strong> is expected (e.g., in SELECT, WHERE, SET).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT employee_name\nFROM employees\nWHERE salary &gt; (\n  SELECT AVG(salary) FROM employees\n);<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u2705 Filters employees earning <strong>above average<\/strong> salary.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Row Subquery \u2013 One Row, Multiple Columns<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Used to compare a row with another row.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT employee_id, first_name\nFROM employees\nWHERE (department_id, job_id) = (\n  SELECT department_id, job_id\n  FROM employees\n  WHERE employee_id = 101\n);<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u2705 Returns employees who share both department and job with employee 101.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Table Subquery \u2013 Used in FROM<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Returns a <strong>full table-like result<\/strong> that can be queried.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT dept_name, emp_count\nFROM (\n  SELECT department_id, COUNT(*) AS emp_count\n  FROM employees\n  GROUP BY department_id\n) AS dept_summary\nJOIN departments d ON d.department_id = dept_summary.department_id;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u2705 You can treat the subquery as a temporary table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd39 Subquery in SELECT<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT\n  employee_name,\n  (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS dept_name\nFROM employees e;<\/code><\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83e\udde0 Quick Recap<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Type<\/th><th>Description<\/th><th>Returns<\/th><\/tr><\/thead><tbody><tr><td>Scalar<\/td><td>One value<\/td><td>Single value<\/td><\/tr><tr><td>Row<\/td><td>One full row<\/td><td>Multiple columns<\/td><\/tr><tr><td>Table<\/td><td>Treated like a table in the main query<\/td><td>Rows + columns<\/td><\/tr><tr><td>Use in<\/td><td>SELECT, FROM, WHERE, HAVING<\/td><td>Anywhere in query<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">\ud83d\udca1 Subqueries add <strong>power and flexibility<\/strong> \u2014 ideal for comparisons and reusable logic.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A subquery is a query inside another query. It\u2019s used to fetch intermediate results for comparison, filtering, or transformation. Types of subqueries: \ud83d\udd39 Scalar Subquery \u2013 One Value Used where a single value is expected (e.g., in SELECT, WHERE, SET). \u2705 Filters employees earning above average salary. \ud83d\udd39 Row Subquery \u2013 One Row, Multiple Columns [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,979],"class_list":["post-3963","cposts","type-cposts","status-publish","hentry","category-notes","category-sql"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3963","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=3963"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3963"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}