SQL for Beginners — Episode 3: JOINs, Subqueries and Multi-Table Queries (Updated June 2026)

TCS laid off 12,000 employees in July 2025 and simultaneously posted 8,400 data analyst openings — every single one of which requires SQL JOIN proficiency. That is the data reality of the Indian IT market in 2025–26: companies are restructuring away from routine roles and hiring aggressively into data roles that require real SQL skills. If you completed Episode 2 and can write a WHERE clause and GROUP BY query, Episode 3 is the episode that takes you from beginner to someone who can answer a technical screening question at Infosys, Persistent Systems or Capgemini without breaking a sweat. JOINs, subqueries and CTEs are the tools that make SQL genuinely powerful.

▶ Watch on YouTube

What Episode 3 Covers and Why JOINs Are the Gateway to Real SQL Work

Episodes 1 and 2 work with single tables. Real-world databases almost never store everything in one table — customers live in a customers table, their orders in an orders table, products in a products table and stock in an inventory table. Answering the question "which customers placed orders for out-of-stock products last quarter?" requires joining four tables together. JOINs are the mechanism that makes relational databases relational. What most people do not realise is that once you understand the mental model — tables are sets, JOIN is set intersection with a condition — every JOIN variant becomes intuitive. Episode 3 builds that mental model and then applies it to the multi-table queries that appear in screening assessments at Infosys Hinjewadi, TCS Kharadi and Cognizant Magarpatta.

INNER JOIN — The Workhorse of Relational Queries

An INNER JOIN returns only rows where the join condition is TRUE in both tables. SELECT customers.name, orders.order_date FROM customers INNER JOIN orders ON customers.id = orders.customer_id returns only customers who have at least one order — customers with no orders are excluded. This is the most common JOIN in production systems: reports and dashboards typically want matched data. INNER JOIN can chain multiple tables: FROM customers JOIN orders ON ... JOIN products ON ... JOIN inventory ON ... returning only rows with a match across all four tables. The query optimiser in MySQL, PostgreSQL and SQL Server handles multi-table JOINs efficiently when join columns are indexed, which is why DBA-level interview questions often ask about index design alongside JOIN syntax.

LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN — When You Need the Gaps Too

LEFT JOIN (also written LEFT OUTER JOIN) returns all rows from the left table, with matching right-table data where available and NULLs where there is no match. SELECT customers.name, orders.order_date FROM customers LEFT JOIN orders ON customers.id = orders.customer_id returns every customer, including those who have never placed an order — their order_date column shows NULL. This is invaluable for identifying gaps: customers who never ordered (churn risk), employees with no assigned manager, products with no sales in a period. RIGHT JOIN is the mirror — all rows from the right table, NULLs for unmatched left rows. FULL OUTER JOIN returns all rows from both tables. Trust me, 80% of real analyst queries use INNER JOIN or LEFT JOIN — master these two and the others follow easily.

Subqueries — Queries Inside Queries for Complex Logic

A subquery is a SELECT statement inside another SELECT, FROM, WHERE or HAVING clause. A scalar subquery returns exactly one value: SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) finds the highest-paid employee. A column subquery returns one column of values used in an IN condition: WHERE department_id IN (SELECT id FROM departments WHERE location = 'Pune'). A table subquery (or derived table) returns a result set used as a temporary table in the FROM clause: FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS summary. Correlated subqueries reference the outer query's current row — they are powerful but execute once per outer row, which can be slow on large tables. Knowing when to replace a correlated subquery with a JOIN is a senior SQL interview question at KPIT, Capgemini and Wipro Digital.

CTEs (Common Table Expressions) — The Professional Way to Write Complex SQL

A CTE (Common Table Expression), written using the WITH keyword, lets you name a subquery and reference it by name in the main query. WITH high_value_orders AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 50000) SELECT customers.name, high_value_orders.total FROM customers JOIN high_value_orders ON customers.id = high_value_orders.customer_id — this is exactly the kind of readable, self-documenting query that senior developers write at Persistent Systems, Accenture and TCS. CTEs also enable recursive queries for hierarchical data (manager-to-employee chains, parts BOMs), which is the most advanced Episode 3 skill. The good news is that MySQL 8.0 plus, PostgreSQL 8.4 plus, SQL Server 2005 plus and Oracle 9i plus all support CTEs — so this is a portable skill.

Real-World SQL Practice and Analyst Jobs in Pune That Require Episode 3 Skills

Episode 3 skills are the minimum bar for a data analyst screening test at Infosys, TCS, Cognizant, Wipro, Accenture, Capgemini, Persistent Systems and KPIT Technologies in Pune. A typical screening question: write a query returning customers who placed orders in January but not in February. The answer requires a LEFT JOIN or a correlated NOT EXISTS subquery — exactly the patterns Episode 3 teaches. Fresher data analyst salaries in Pune: Rs 3.5–6 LPA for roles requiring SQL JOIN proficiency (AmbitionBox 2025). Mid-level analysts with CTE and window function skills earn Rs 7–12 LPA. Our AI Powered Application Development workshop at ABC Trainings covers SQL from Episode 1 through joins, CTEs, window functions and stored procedures at Wagholi (Laxmi Datta Arcade, Pune), Hadapsar (Shree Tower, Magarpatta), Cidco N-1 and Osmanpura (Sambhajinagar), and Sangli (Shubham Emphoria, Vishrambag, weekend batches available). WhatsApp 7774002496 or call 7039169629.