SQL JOINs and Subqueries: Connect Related Data

SQL beginner course · Chapter 7 of 10

SQL JOINs and Subqueries: Connect Related Data explained in simple steps. Examples use practice tables such as students and scores; adapt names to your database.

Follow the concepts from left to right, then run the examples below.

In this lesson

1. INNER JOIN
2. LEFT and outer joins
3. Non-equi joins
4. Subqueries and EXISTS
5. UNION and UNION ALL

INNER JOIN

INNER JOIN returns rows where both tables match the ON condition. A missing or wrong condition can produce too many rows.

Example

SELECT s.student_name, c.points FROM students s INNER JOIN scores c ON c.student_id = s.student_id;

Expected result: Only students with matching scores appear.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

LEFT and outer joins

LEFT JOIN keeps every row from the left table, adding NULLs for missing right-side values. RIGHT and FULL joins are supported by some databases, but not all.

Example

SELECT s.student_name, c.points FROM students s LEFT JOIN scores c ON c.student_id = s.student_id;

Expected result: Students without scores also appear.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Non-equi joins

A join condition can use a comparison other than equality. Use it carefully because one row may match several ranges.

Example

SELECT s.student_name, b.band_name FROM scores s JOIN score_bands b ON s.points BETWEEN b.min_points AND b.max_points;

Expected result: Each score receives its matching band.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Subqueries and EXISTS

A subquery supplies a value or set to another query. EXISTS asks whether at least one related row is present.

Example

SELECT student_name FROM students s WHERE EXISTS (SELECT 1 FROM scores c WHERE c.student_id = s.student_id);

Expected result: Students with at least one score appear.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

UNION and UNION ALL

UNION combines compatible SELECT results and removes duplicates. UNION ALL keeps duplicates and is often faster.

Example

SELECT student_name FROM current_students UNION ALL SELECT student_name FROM alumni;

Expected result: Names from both tables are listed, including repeats.

Practice: Predict the result, run the query on a practice database, then change one part and explain what changed. For write commands, use disposable data.

Quick review

Explain each concept in your own words. Recreate one example without looking at the code. Compare your result with the expected result above.

Browse the full SQL learning path. This is original teaching material; the syllabus was checked against the w3resource SQL tutorial index.