Joins and Nested Queries

Definition

A join is an SQL operation used to combine rows from two or more tables based on a related column, usually a primary key and foreign key relationship.

A nested query or subquery is a query written inside another SQL query. The inner query provides values or conditions to the outer query, allowing complex data retrieval in a structured way.

Main Content

1. Joins

Purpose and meaning

• : Joins are used when information is spread across multiple tables. Instead of storing all data in one large table, databases split data into related tables. For example, a Students table may contain student details, while an Enrollments table contains course registration information. A join combines these tables so the result can show which student enrolled in which course.

Types of joins with examples

• :
• INNER JOIN: Returns only the matching rows from both tables.

    SELECT Students.StudentName, Enrollments.CourseID
    FROM Students
    INNER JOIN Enrollments
    ON Students.StudentID = Enrollments.StudentID;

• LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If there is no match, the right table columns contain NULL.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL OUTER JOIN: Returns all rows from both tables, matching where possible.
• CROSS JOIN: Produces all possible combinations of rows from both tables.

How joins work conceptually

• : Join conditions are usually written using ON. This condition specifies how rows from one table relate to rows from another table. The database engine compares rows and builds a combined result set according to the join type.

Example tables

• :

Students | StudentID | StudentName | |----------|-------------| | 1 | Asha | | 2 | Ravi |

Enrollments | StudentID | CourseID | |----------|----------| | 1 | CS101 | | 3 | MA102 |

An INNER JOIN returns only the row for Asha because StudentID 1 appears in both tables.

2. Nested Queries

Meaning and structure

• : A nested query is a query inside another query. The inner query is executed first in many cases, and its output is used by the outer query. Nested queries help break a large problem into smaller logical parts.

Types of nested queries

• :
• Single-row subquery: Returns one value and is often used with operators like =, >, <.
• Multiple-row subquery: Returns multiple values and often uses IN, ANY, or ALL.
• Correlated subquery: The inner query depends on values from the outer query and may run once for each row of the outer query.

Examples

• :
• Find students who scored more than the average marks:

    SELECT StudentName
    FROM Students
    WHERE Marks > (SELECT AVG(Marks) FROM Students);

• Find employees in departments that have at least one employee earning above 50000:

    SELECT EmployeeName
    FROM Employees
    WHERE DepartmentID IN (
        SELECT DepartmentID
        FROM Employees
        WHERE Salary > 50000
    );

Why nested queries are useful

• : They are especially helpful for filtering data based on results computed from the same or another table. They also make complex logic easier to express without using multiple temporary tables.

Important operators used with subqueries

• :
• IN for matching any value in a list
• EXISTS to test whether the subquery returns rows
• ANY and ALL for comparing against sets of values

3. Joins vs Nested Queries

Purpose difference

• : Joins combine data from tables side by side, while nested queries often compute a condition or intermediate result before the outer query runs.

When to use each

• :
• Use a join when you need related columns from multiple tables in the final output.
• Use a nested query when you need to filter, aggregate, or compare data based on a separate result.

Performance and readability

• :
• Joins are often faster and more efficient in many database systems, especially for large datasets.
• Nested queries can sometimes be easier to understand for step-by-step logic, but some nested queries may be less efficient depending on the database optimizer.

Equivalent examples

• :

Using a join:

  SELECT S.StudentName
  FROM Students S
  INNER JOIN Enrollments E
  ON S.StudentID = E.StudentID;

Using a nested query:

  SELECT StudentName
  FROM Students
  WHERE StudentID IN (
      SELECT StudentID
      FROM Enrollments
  );

Both queries can produce similar results, but the join directly combines tables, whereas the subquery first finds matching StudentIDs.

Working / Process

1. Identify the data requirement

• : Determine whether the task is to combine information from multiple tables or to filter data based on another query result.

2. Choose the appropriate SQL structure

• : Use a join when you need merged rows from related tables, or use a nested query when one query must depend on the output of another.

3. Write and execute the query

• : Define the tables, select the required columns, specify the join condition or subquery condition, and then verify the output carefully.

Advantages / Applications

• Helps retrieve related data from multiple tables without duplicating information
• Makes complex data analysis possible, such as finding averages, comparisons, and conditional matches
• Widely used in reports, dashboards, inventory systems, payroll processing, academic records, and customer databases

Summary

• Joins combine rows from related tables
• Nested queries place one query inside another
• Both are used to solve complex SQL retrieval tasks
• Terms to remember: INNER JOIN, LEFT JOIN, subquery, correlated subquery