SQL JOINs, Subqueries & Aggregate Functions — SQL Beginner's Guide Episode 4 (Updated June 2026) (Updated June 2026)

If Episode 3 got you comfortable with basic SELECT, INSERT, UPDATE, and DELETE, Episode 4 is where SQL starts to feel genuinely powerful. JOINs, subqueries, and aggregate functions are what transform SQL from a simple lookup tool into the query language that drives business analytics, hiring decisions, and product reports at companies like Infosys, TCS, and every major Indian startup. NASSCOM-Deloitte projects 1.25 million tech professionals needed by 2027, and data analyst and data engineering roles — which require fluent SQL — are among the fastest-growing segments. Episode 4 of our SQL Beginner's Guide covers JOIN types, GROUP BY with HAVING, aggregate functions, and correlated subqueries, with practical examples you can run in MySQL or PostgreSQL today.

SQL JOINs — Combining Data From Multiple Tables

A SQL JOIN combines rows from two or more tables based on a related column. In almost every real-world database, data is split across multiple tables — a customers table, an orders table, a products table — and JOINs are how you bring that data together into meaningful reports. The classic example: a customer service team at Bajaj Auto needs to see customer names alongside their order details. The customer name is in the customers table; the order details are in the orders table. A JOIN on customer_id in both tables produces the combined view. Without JOINs, you'd need to run multiple queries and manually combine results in your application code — inefficient, error-prone, and slow.

INNER JOIN vs LEFT JOIN vs RIGHT JOIN — Practical Differences

INNER JOIN returns only the rows where the join condition is met in both tables — if a customer has no orders, they don't appear in the result. LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right — customers with no orders appear with NULL values in the order columns. This is critical for reporting: a marketing team at TCS might want a list of all customers who have never placed an order to target them with a promotion. RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN returns all rows from both tables regardless of matches. In practice, INNER JOIN and LEFT JOIN cover 90% of real-world JOIN use cases.

JOIN Type	Returns	Unmatched Left?	Unmatched Right?
INNER JOIN	Matched rows only	No	No
LEFT JOIN	All left + matched right	Yes (NULLs)	No
RIGHT JOIN	All right + matched left	No	Yes (NULLs)
FULL OUTER JOIN	All rows from both	Yes (NULLs)	Yes (NULLs)

Aggregate Functions — COUNT, SUM, AVG, MIN, MAX

Aggregate functions perform calculations across multiple rows and return a single value. COUNT(*) counts all rows; COUNT(column) counts non-NULL values in that column. SUM(column) adds all values; AVG(column) calculates the mean; MIN and MAX return the smallest and largest values. These functions are the backbone of business reporting — total sales for the month, average order value, highest-paid employee, number of customers per city. Data analysts at Infosys, Wipro, and financial firms run aggregate queries dozens of times a day. Understanding aggregates is non-negotiable for any data analyst, data engineer, or backend developer role in India.

GROUP BY and HAVING — Summarising Data in SQL

GROUP BY groups rows that share the same value in a specified column, allowing aggregate functions to calculate results per group. For example, SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city gives you the number of customers in each city. The HAVING clause filters groups after aggregation — unlike WHERE which filters individual rows before grouping. Example: HAVING COUNT(*) > 10 shows only cities with more than 10 customers. The critical rule: you can only SELECT columns that appear in the GROUP BY clause or are wrapped in an aggregate function. This trips up almost every SQL beginner the first time — if you're getting column not in GROUP BY errors, check that all non-aggregate SELECT columns are in your GROUP BY clause.

Subqueries — Nesting SELECT Inside SELECT

A subquery is a SELECT statement nested inside another SQL statement. Simple subqueries execute independently and their result is used by the outer query — for example, SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) lists all employees earning above average. A correlated subquery references columns from the outer query, so it executes once for every row the outer query processes — powerful but potentially slow on large tables. Subqueries can appear in WHERE, FROM (as derived tables), and SELECT clauses. When performance matters on large datasets, consider whether a JOIN can replace the subquery — JOINs are often faster because the query optimiser can use indexes more efficiently than with repeated subquery execution.

Maharashtra's CMYKPY (Chief Minister Yuva Karya Prashikshan Yojana) provides apprenticeship stipends of ₹6,000–₹10,000 per month for IT students learning SQL, data analysis, and database development. ABC Trainings helps eligible candidates register and connects them with data-driven companies in Pune, Sambhajinagar, and Sangli for structured industry apprenticeships.

FAQs

What is the difference between INNER JOIN and LEFT JOIN in SQL?

INNER JOIN returns only rows where the join condition matches in both tables — if a row in either table has no match, it's excluded. LEFT JOIN returns all rows from the left table and only matching rows from the right; non-matching right rows appear as NULL. Use INNER JOIN when you only care about records that exist in both tables; use LEFT JOIN when you need all left-table records regardless of whether a match exists on the right.

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before GROUP BY and aggregate functions are applied — it operates on raw row data. HAVING filters groups after GROUP BY and aggregation are done — it operates on aggregated results. Example: WHERE salary > 50000 filters individual employee rows; HAVING AVG(salary) > 50000 filters salary groups (like departments) where the average is above 50,000. You cannot use aggregate functions in WHERE; you must use HAVING for that.

When should I use a subquery vs a JOIN in SQL?

When both produce the same result, prefer JOINs — they are usually faster because the query optimiser can leverage indexes more effectively. Use subqueries when they make the query intent clearer (especially for single-value comparisons), when you need a derived table in the FROM clause, or when the subquery is correlated and must reference the outer query's row. On small datasets the difference is negligible; on tables with millions of rows (like e-commerce orders or bank transactions), the choice between JOIN and subquery can mean seconds vs milliseconds.

What SQL skills do I need to become a data analyst in India?

A data analyst role in India requires: SELECT, WHERE, ORDER BY, and LIMIT for basic queries; INNER JOIN, LEFT JOIN for combining tables; GROUP BY, HAVING, COUNT, SUM, AVG for aggregations; subqueries and CTEs (WITH clauses) for complex queries; and window functions (ROW_NUMBER, RANK, LAG, LEAD) for advanced analytics. Tools like MySQL, PostgreSQL, and SQL Server are all relevant. Data analyst roles in Pune, Hyderabad, and Bengaluru pay ₹4–10 LPA at entry level, rising to ₹15–25 LPA with 3–5 years experience.