10. Subqueries Reference

Nested query scopes.

Scalar Subquery

Return: Value

Query yielding exactly 1 row and 1 column value.

Syntax signature:SELECT * FROM t WHERE val = (SELECT scalar FROM t2);

Code snippet:

python

SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

Expected Output:Highest earning employee.

Remember: Always ensure correct syntax formatting when calling Scalar Subquery.

Correlated Subquery

Return: Value

Query referencing outer column context.

Syntax signature:SELECT * FROM t o WHERE val > (SELECT AVG(val) FROM t WHERE cat = o.cat);

Code snippet:

python

SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

Expected Output:Employees earning above their department average.

Remember: Always ensure correct syntax formatting when calling Correlated Subquery.

EXISTS

Return: Value

Check if subquery returns at least one row.

Syntax signature:SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id);

Code snippet:

python

SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Expected Output:Active customers list.

Remember: Always ensure correct syntax formatting when calling EXISTS.

NOT EXISTS

Return: Value

Verify subquery returns zero rows.

Syntax signature:SELECT * FROM t WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id);

Code snippet:

python

SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Expected Output:Customers with no orders.

Remember: Always ensure correct syntax formatting when calling NOT EXISTS.

Return: Value

Check membership inside array/subquery array.

Syntax signature:SELECT * FROM t WHERE val IN (SELECT col FROM t2);

Code snippet:

python

SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

Expected Output:Products from active categories.

Remember: Always ensure correct syntax formatting when calling IN.