Types of Joins

• INNER JOIN – Returns matching rows from both tables.

• LEFT JOIN (LEFT OUTER JOIN) – Returns all rows from the left table + matching rows from the right.

• RIGHT JOIN (RIGHT OUTER JOIN) – Returns all rows from the right table + matching rows from the left.

• FULL OUTER JOIN – Returns all rows from both tables, with NULLs for non-matching rows.

• SELF JOIN – A table joined with itself (e.g., for hierarchy or reporting relationships).

• CROSS JOIN – Returns the Cartesian product (use cautiously).

 

Key Concepts

• ON clause defines how the tables are related.

• Use table aliases to simplify queries, especially when joining multiple tables.

• NULLs appear when one side of a join has no match (in outer joins).

• Joins can be combined with WHERE, GROUP BY, and ORDER BY for complex queries.

JOIN Fundamentals 

🔹 How to Use Joins

• • Use ON clause to specify the matching condition between tables.

  • Always match primary key ↔ foreign key or related columns.

  • Use aliases (e.g., A, B) for cleaner syntax.

  • Syntax:
    SELECT columns FROM TableA A JOIN TableB B ON A.ID = B.ID

 

• Key Points to Take Care Of

  • Matching Column Data Types – Must be compatible.

  • Avoid Ambiguity – Qualify column names with table names or aliases.

  • Duplicate Columns – Use SELECT A.col1, B.col2 instead of SELECT * when columns repeat.

  • Performance – Avoid unnecessary joins, especially with large tables.

🚫 Non-Matching IDs

• Using LEFT JOIN + WHERE right_table.column IS NULL to identify records in one table not present in another.

➕ Joining More Than Two Tables

• Cascading joins using appropriate aliases.

• Order of joins and impact on result sets.

Limitations of Joins

• Incorrect joins = incorrect data (especially in OUTER JOINs)

• Can return large result sets – slow without filtering.

• JOIN on NULL fails – use IS NULL logic for outer joins.

• Complex joins on multiple tables can get hard to debug.

✅ How to Overcome Limitations

• Add WHERE clause to filter early and reduce result size.

• Use EXISTS or IN for subqueries instead of joins in some cases.

• Create indexes on join columns to improve speed.

• Use EXPLAIN or Execution Plan to analyze join performance.

• Break complex joins into intermediate temp tables if needed.

 

⚖️ UNION vs UNION ALL

• UNION removes duplicates.

• UNION ALL keeps all records (better performance if duplicates aren’t a concern).

🔁 Join Between Queries

• Joining result sets from subqueries or CTEs (Common Table Expressions).

• Example: Joining customer totals from one subquery to discount levels from another.

🧠 Advanced Join Concepts

• Offset Logic – e.g., Linking row n of table1 with row n+1 of table2.

  • Use of ROW_NUMBER() and self-joins for such scenarios.

💼 Interview Questions (Examples)

• Difference between INNER JOIN and OUTER JOIN.

• What is a self join and when would you use it?

• How to get records from table A not in table B?

• When to use UNION ALL instead of UNION?

• Explain a real-world use case of a LEFT JOIN.

🧩 Practice Quiz for you with Solutions