SQL Queries: Joins, Subqueries and Views With easy examples.

SQL queries are how you ask a database questions. Joins combine rows from two or more tables through linked keys, subqueries nest one query inside another so the inner result feeds the outer one, and views save a query under a name so you can reuse it like a virtual table.

They matter because real data is always spread across tables — employees in one, departments in another. Without joins you would fetch data in pieces and stitch it together by hand. These three tools turn hundred-line data-wrangling scripts into a single readable query.

Key Concepts

• INNER JOIN: only rows matching in both tables.
• LEFT JOIN: all rows from the left table, NULLs where the right has no match — finds "departments with zero employees".
• ON vs WHERE: ON defines how tables link; WHERE filters the combined result.
• Subquery: a SELECT inside another — in WHERE, in FROM (derived table), or in the SELECT list.
• Correlated subquery: the inner query references the outer row, re-running per row — powerful but slow on big tables.
• View: a stored query behaving like a table. It stores no data, just the query text, so it always shows current data.
• NULL: the absence of a value. Never use = NULL — always IS NULL.

How It Works

A join logically pairs rows from both tables per the ON condition, then WHERE filters the pairs, then SELECT picks columns. The optimizer usually avoids building the full cross product using indexes or hash tables, but the result is identical. A subquery in WHERE runs first and hands its result outward — "employees earning above the average salary" computes the average once, then compares each employee. A view is just saved SELECT text: querying it substitutes the text and runs it fresh every time.

Worked Example

Employees(EmpID, Name, DeptID, Salary): (1, Ana, 10, 50000), (2, Dev, 20, 60000), (3, Ira, 10, 55000), (4, Om, 20, 70000), (5, Raj, NULL, 45000). Departments: (10, Sales), (20, Tech).

1. INNER JOIN on DeptID: Raj's NULL matches nothing → 4 rows. Raj is dropped.
2. LEFT JOIN from employees: all 5 rows; Raj's DeptName is NULL — correctly flagging "no department".
3. Subquery: average salary = (50000+60000+55000+70000+45000) ÷ 5 = 280000 ÷ 5 = 56000. Above-average earners: Dev (60000), Om (70000) — 2 rows.
4. GROUP BY: Sales average = (50000+55000) ÷ 2 = 52500; Tech = (60000+70000) ÷ 2 = 65000.
5. View: CREATE VIEW TechStaff AS SELECT Name, Salary FROM Employees WHERE DeptID = 20. SELECT AVG(Salary) FROM TechStaff = 65000, always current.

Common Misconceptions

• "JOIN and UNION are the same." JOIN glues tables side by side via keys (wider rows); UNION stacks result sets vertically (same columns, more rows).
• "A view stores a copy of the data." A normal view stores only the query and runs it fresh — always up to date, but as slow as the underlying query.
• "NULL equals NULL." NULL = NULL is never true — it is unknown. That is why unmatched rows need IS NULL checks.

Real-World Applications

• Dashboards: reports join orders, customers, and products, aggregating per region.
• Access control: views expose safe columns (name) while hiding salary from some users.
• Data cleaning: LEFT JOINs find orphans — orders with no customer.
• Migrations: views give old apps a stable interface while tables are restructured.

Joins assemble the full picture from scattered tables, subqueries let one question feed another, and views package useful questions into reusable names. Trace each row through ON, WHERE, and SELECT on tiny tables, and real-world queries stop feeling like magic.