SQL Joins with Examples: Types, Practice Questions and Interview Qs

SQL joins combine rows from two or more tables using a related column. They are asked in almost every SQL interview in India, for freshers, data analysts, testers and developers alike. This guide explains each join type with the same two small tables so you can see exactly what changes.

The Example Tables

students

courses

Note: Meena's course 30 does not exist in courses, Karan has no course, and nobody is in Power BI (40).

INNER JOIN: Only Matching Rows

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON c.course_id = s.course_id;

LEFT JOIN: All Rows from the Left Table

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON c.course_id = s.course_id;

Add WHERE c.course_id IS NULL to keep only students without a valid course: the classic "find missing matches" pattern.

RIGHT JOIN: All Rows from the Right Table

SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON c.course_id = s.course_id;

Most people write this as a LEFT JOIN with the tables swapped; the result is the same.

FULL OUTER JOIN: Everything from Both

SELECT s.name, c.course_name
FROM students s
FULL OUTER JOIN courses c ON c.course_id = s.course_id;

MySQL has no FULL OUTER JOIN; combine a LEFT JOIN and a RIGHT JOIN with UNION.

CROSS JOIN and SELF JOIN

CROSS JOIN returns every combination: 4 students × 3 courses = 12 rows. Useful for generating test combinations, dangerous by accident.

SELF JOIN joins a table to itself, for example employees to their managers:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

Join Types at a Glance

Common Join Mistakes

• Duplicate keys multiply rows. If a course_id appears twice in courses, each matching student appears twice. Always check key uniqueness.
• Filtering the right table in WHERE turns a LEFT JOIN into an INNER JOIN. Put such conditions in the ON clause instead.
• NULLs never match in an equality join; NULL = NULL is not true.
• Forgetting the join condition creates an accidental cross join.

How Testers Use Joins

ETL and database testers use joins every day: LEFT JOIN with IS NULL finds orphan records and missing data, and INNER JOIN on keys compares values between source and target. See SQL for testers and ETL testing SQL queries.

Practice Questions with Answers

1. List students and their course names, including students without a course.

LEFT JOIN students to courses (query in the LEFT JOIN section).

2. Find courses with no students.

SELECT c.course_name FROM courses c LEFT JOIN students s ON s.course_id = c.course_id WHERE s.student_id IS NULL; returns Power BI.

3. Find students whose course_id does not exist in courses.

LEFT JOIN with WHERE c.course_id IS NULL AND s.course_id IS NOT NULL; returns Meena.

4. Count students per course, including courses with zero students.

SELECT c.course_name, COUNT(s.student_id) FROM courses c LEFT JOIN students s ON s.course_id = c.course_id GROUP BY c.course_name; COUNT(column) ignores NULLs, so Power BI shows 0.

5. How many rows does students CROSS JOIN courses return?

12 (4 × 3).

SQL Joins Interview Questions

6. What is the difference between INNER JOIN and LEFT JOIN?

INNER returns only matching rows; LEFT returns all rows from the left table with NULLs where there is no match.

7. Can a join return more rows than either table?

Yes, when the join key is duplicated on one or both sides, rows multiply.

8. What is the difference between a join and UNION?

Joins combine columns side by side from related tables; UNION stacks rows from queries with the same columns.

9. What is a natural join?

A join on all columns with the same name in both tables. It is risky because new same-named columns change the result.

10. How do you find records in A not in B?

LEFT JOIN B and filter B.key IS NULL, or use NOT EXISTS, or EXCEPT/MINUS on the keys.

More: SQL interview questions for freshers.

Data testing skills: SQL for testers · data migration testing · Power BI testing · big data testing · data quality checks · ETL tools list · ADF & Databricks testing · database testing interview questions · Python for testers · data reconciliation · test data management · ETL defects examples · Snowflake ETL testing · AWS Glue testing · data lake testing

More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi) · ETL testing projects for practice · ETL testing notes · work from home ETL jobs

Frequently Asked Questions

What are the types of joins in SQL?

INNER JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL OUTER JOIN, CROSS JOIN and SELF JOIN.

What is the most commonly used SQL join?

INNER JOIN and LEFT JOIN are the most common. LEFT JOIN with an IS NULL filter is widely used to find missing matches.

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

There is no difference; OUTER is optional.

Why does my join return duplicate rows?

Because the join key is not unique in one of the tables, so each match produces a row. Check key uniqueness with GROUP BY and HAVING COUNT(*) > 1.

Does MySQL support FULL OUTER JOIN?

No. Emulate it with a LEFT JOIN UNION a RIGHT JOIN.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures