Subqueries & Common Table Expressions

📄 MySQLLesson 8Intermediate

A subquery is a query nested inside another SQL statement. Common Table Expressions (CTEs) define temporary result sets accessible within statements.

1 Subqueries & CTEs Syntax

SQL — Subqueries & CTEs

# Subquery lookup
SELECT title FROM posts 
WHERE user_id = (SELECT id FROM users WHERE username = 'balaji');

# CTE Definition
WITH user_post_counts AS (
    SELECT user_id, COUNT(id) AS post_count
    FROM posts
    GROUP BY user_id
)
SELECT users.username, user_post_counts.post_count
FROM users
INNER JOIN user_post_counts ON users.id = user_post_counts.user_id;

2 Code Challenge

Challenge: Write a query finding all products priced higher than the average price of all database items, utilizing a nested subquery.