Joins in SQL in DBMS Theory - Full Explanation

Introduction

Imagine you have two lists of information, like one with customer names and another with their orders, but you want to see them together in one view. Joins in SQL solve this problem by combining data from two or more tables based on related columns.

Explanation

Inner Join

Inner Join returns only the rows where there is a match in both tables based on a common column. It filters out rows that do not have matching values in both tables.

Inner Join shows only the matching data from both tables.

Left Join (Left Outer Join)

Left Join returns all rows from the left table and the matched rows from the right table. If there is no match, the result will have NULLs for the right table's columns.

Left Join keeps all data from the left table, adding matches or NULLs from the right.

Right Join (Right Outer Join)

Right Join returns all rows from the right table and the matched rows from the left table. If there is no match, the left table's columns will have NULLs.

Right Join keeps all data from the right table, adding matches or NULLs from the left.

Full Join (Full Outer Join)

Full Join returns all rows when there is a match in one of the tables. It combines Left and Right Joins, showing all data from both tables with NULLs where there is no match.

Full Join shows all data from both tables, matching where possible and filling NULLs otherwise.

Cross Join

Cross Join returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table. It does not require a matching column.

Cross Join pairs every row of one table with every row of the other.

Real World Analogy

Imagine two lists: one of students and another of clubs they might join. Inner Join is like listing only students who have joined clubs. Left Join is like listing all students and showing their clubs if any, otherwise blank. Right Join is like listing all clubs and showing members if any, otherwise blank. Full Join is like listing all students and all clubs, matching where possible. Cross Join is like pairing every student with every club to see all possible combinations.

Inner Join → Students who have actually joined clubs, showing only those with matches

Left Join → All students listed, with their clubs if they joined any, else blank

Right Join → All clubs listed, with members if any, else blank

Full Join → All students and all clubs listed, matched where possible, blanks where not

Cross Join → Every student paired with every club, showing all possible pairs

Diagram

Diagram

┌─────────────┐       ┌─────────────┐
│  Table A    │       │  Table B    │
│ (Students)  │       │  (Clubs)    │
└─────┬───────┘       └─────┬───────┘
      │                     │
      │                     │
      │    ┌─────────────┐  │
      ├────│ Inner Join  │──┤
      │    └─────────────┘  │
      │                     │
      │    ┌─────────────┐  │
      ├────│ Left Join   │  │
      │    └─────────────┘  │
      │                     │
      │    ┌─────────────┐  │
      ├────│ Right Join  │  │
      │    └─────────────┘  │
      │                     │
      │    ┌─────────────┐  │
      ├────│ Full Join   │  │
      │    └─────────────┘  │
      │                     │
      │    ┌─────────────┐  │
      └────│ Cross Join  │──┘
           └─────────────┘

Diagram showing two tables and the five types of joins connecting them.

Key Facts

Inner Join → Returns rows with matching values in both tables only.

Left Join → Returns all rows from the left table and matched rows from the right.

Right Join → Returns all rows from the right table and matched rows from the left.

Full Join → Returns all rows from both tables, matching where possible.

Cross Join → Returns all possible combinations of rows from both tables.

Code Example

DBMS Theory

CREATE TABLE Students (ID INT, Name TEXT);
CREATE TABLE Clubs (ID INT, ClubName TEXT);

INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Clubs VALUES (1, 'Chess'), (2, 'Music');

-- Inner Join: Students who joined clubs
SELECT Students.Name, Clubs.ClubName
FROM Students
INNER JOIN Clubs ON Students.ID = Clubs.ID;

-- Left Join: All students with their clubs if any
SELECT Students.Name, Clubs.ClubName
FROM Students
LEFT JOIN Clubs ON Students.ID = Clubs.ID;

-- Cross Join: Every student with every club
SELECT Students.Name, Clubs.ClubName
FROM Students
CROSS JOIN Clubs;

Common Confusions

Believing Left Join and Right Join return the same results.

Believing Left Join and Right Join return the same results. Left Join keeps all rows from the left table; Right Join keeps all rows from the right table, so results differ depending on which table is on which side.

Thinking Full Join only returns matched rows.

Thinking Full Join only returns matched rows. Full Join returns all rows from both tables, including unmatched rows with NULLs where no match exists.

Assuming Cross Join requires a matching column.

Assuming Cross Join requires a matching column. Cross Join does not use any matching column; it pairs every row from one table with every row from the other.

Summary

Joins combine rows from two tables based on related columns to show combined data.

Inner Join shows only matching rows; Left and Right Joins keep all rows from one table and match from the other.

Full Join shows all rows from both tables; Cross Join pairs every row from one table with every row from the other.