Joins: Inner, Outer, Cross Joins

1. What Are Joins?

  • Joins: SQL operations used to combine rows from two or more tables based on a related column.
  • Types of Joins:
    • Inner Join
    • Outer Join (Left, Right, Full)
    • Cross Join


2. Inner Join (Matching Pairs Only)

  • Definition: Returns rows where there is a match in both tables based on the join condition. Keeps only rows where there is a match in both tables.
  • Syntax: SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.DeptID;
NameDeptName
JohnHR
AliceIT
  • “Show employees and their departments if they belong to a department.”
  • Key Points:
    • Excludes unmatched rows.
    • Most commonly used type of join.

3. Outer Joins

a. Left Outer Join (Keep All From the Left)

  • Definition: Returns all rows from the left table, and the matching rows from the right table. Unmatched rows from the right table are filled with NULL.
  • Syntax: SELECT columns FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.DeptID;
NameDeptName
JohnHR
AliceIT
BobNULL
  • Show all employees and their departments. If no department is assigned, show ‘NULL’.
  • Key Point: Includes all rows from the left table, even if there is no match.

b. Right Outer Join (Keep All From the Right)

  • Definition: Returns all rows from the right table, and the matching rows from the left table. Unmatched rows from the left table are filled with NULL.
  • Syntax: SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
SELECT Employees.Name, Departments.DeptName
FROM Employees
RIGHT JOIN Departments ON Employees.DeptID = Departments.DeptID;
NameDeptName
JohnHR
AliceIT
NULLMarketing
  • Show all departments and the employees in them. If no employees, show ‘NULL’.
  • Key Point: Includes all rows from the right table, even if there is no match.

c. Full Outer Join (Everyone’s Included)

  • Definition: Returns all rows when there is a match in either table, and fills unmatched rows with NULL.
  • Syntax: SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
SELECT Employees.Name, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;
NameDeptName
JohnHR
AliceIT
BobNULL
NULLMarketing
  • Show all employees and all departments, even if they don’t match
  • Key Point: Combines results of Left Join and Right Join.

4. Cross Join (Every Combination)

  • Definition: Returns the Cartesian product of two tables (i.e., every row from the first table is paired with every row from the second table). Pair everyone in Group A with everyone in Group B.
  • Syntax: SELECT columns FROM table1 CROSS JOIN table2;
SELECT Employees.Name, Departments.DeptName
FROM Employees
CROSS JOIN Departments;
NameDeptName
JohnHR
JohnIT
JohnMarketing
AliceHR
AliceIT
AliceMarketing
BobHR
BobIT
BobMarketing
  • Pair every employee with every department (no real-world logic here).
  • Key Points:
    • No condition is required.
    • Produces a large number of rows (rows in Table1 × rows in Table2).

Comparison Table

Quick Comparison

Join TypeWhat It Does
Inner JoinMatch and show rows that exist in both tables.
Left JoinAll rows from the left table, match or no match.
Right JoinAll rows from the right table, match or no match.
Full Outer JoinAll rows from both tables, match or no match.
Cross JoinEvery row from Table 1 pairs with every row in Table 2.

Join TypeIncludes Unmatched RowsMatch RequirementCommon Use Case
Inner JoinNoOnly rows with matching values in both tablesFiltering matching data.
Left Outer JoinYes (Left Table)All rows from the left, matched or unmatchedEnsure all rows from the left table.
Right Outer JoinYes (Right Table)All rows from the right, matched or unmatchedEnsure all rows from the right table.
Full Outer JoinYes (Both Tables)All rows from both, matched or unmatchedCombine results of left and right join.
Cross JoinN/ANo condition; Cartesian productPairing all rows from both tables.

Exam Tips

  1. Memorize the syntax and behavior of each join type.
  2. Understand the differences between Inner and Outer Joins (Left, Right, Full).
  3. Remember that Cross Join does not use a condition and produces a Cartesian product.
  4. Practice queries with real-world scenarios to solidify concepts.

MCQ

Q1. What is the primary purpose of a SQL Join?
a) To create new tables
b) To combine rows from two or more tables based on a related column
c) To delete rows in a table
d) To modify the structure of a table

Answer: b

Q2. Which of the following is NOT a type of join in SQL?
a) Inner Join
b) Outer Join
c) Cross Join
d) Merge Join

Answer: d

Q3. A join that returns only the rows with matching values in both tables is called:
a) Left Outer Join
b) Full Outer Join
c) Cross Join
d)..lpao Inner Join

Answer: d

Q4. An INNER JOIN will return rows:
a) With matching values from both tables
b) Only from the first table
c) Only from the second table
d) All rows from both tables regardless of matching

Answer: a

Q5. Given two tables, Students and Classes, the result of the following query will be?

SELECT Students.Name, Classes.ClassName FROM Students INNER JOIN Classes ON Students.ClassID = Classes.ClassID;

a) All students with their respective class names
b) Only students who belong to classes with matching ClassID
c) All students and all class names
d) Students with NULL ClassID included

Answer: b

Q6. What does a LEFT JOIN return?
a) All rows from the left table and matching rows from the right table
b) Only matching rows from both tables
c) All rows from the right table and matching rows from the left table
d) All rows from both tables

Answer: a

Q7. In a LEFT JOIN, if there is no match in the right table, what will be displayed in the result?
a) The corresponding column will have default values
b) The row is excluded
c) NULL values will be displayed
d) The query throws an error

Answer: c

Q8. A RIGHT JOIN is similar to a LEFT JOIN, except it:
a) Includes all rows from the right table and matches from the left table
b) Includes all rows from the left table and matches from the right table
c) Includes only matching rows
d) Includes rows from neither table

Answer: a

Q9. In the case of a RIGHT JOIN, unmatched rows in the left table will:
a) Be excluded from the result
b) Appear with NULL values in their columns
c) Throw an error
d) Appear with default values

Answer: b

Q10. A FULL OUTER JOIN returns:
a) Only rows with matches in both tables
b) All rows from the left table
c) All rows from the right table
d) All rows from both tables, with NULLs where no match exists

Answer: d

Q11. Which join combines the results of LEFT JOIN and RIGHT JOIN?
a) Cross Join
b) Full Outer Join
c) Inner Join
d) Natural Join

Answer: b

Q12. What is the result of a CROSS JOIN between two tables with 5 and 10 rows, respectively?
a) 5 rows
b) 10 rows
c) 50 rows
d) No rows

Answer: c

Q13. Which join produces a Cartesian product of two tables?
a) Inner Join
b) Full Outer Join
c) Cross Join
d) Natural Join

Answer: c

Q14. Which join would you use to find rows in one table that do NOT have a corresponding match in another table?
a) Inner Join
b) Outer Join
c) Cross Join
d) Self Join

Answer: b

Q15. What will happen if no ON clause is provided in an INNER JOIN?
a) It will throw an error
b) It will perform a Cross Join instead
c) It will return unmatched rows
d) It will exclude both tables

Answer: b

Q16. Consider two tables:
Table A has 4 rows.
Table B has 6 rows.
What is the maximum number of rows returned by a CROSS JOIN?**
a) 4
b) 6
c) 10
d) 24

Answer: d

Q17. What does the following query achieve?

SELECT E.Name, D.DeptName FROM Employees E LEFT JOIN Departments D ON E.DeptID = D.DeptID WHERE D.DeptID IS NULL;

a) Lists all employees with no department assigned
b) Lists all departments with no employees
c) Lists all employees and their departments
d) Lists all departments with NULL IDs

Answer: a

Q18. A self-join is primarily used to:
a) Join two unrelated tables
b) Find relationships within the same table
c) Create a Cartesian product
d) Merge two identical tables

Answer: b

Q19. In a FULL OUTER JOIN, unmatched rows in both tables are excluded?

Answer: False

Q20. A CROSS JOIN requires a condition to work?

Answer: False