What happens if you confusing left join with right join?

Results include unexpected NULLs or missing rows Fix: Remember LEFT JOIN keeps all rows from left table; RIGHT JOIN keeps all from right

What happens if you forgetting to alias tables in self join?

SQL error due to ambiguous column references Fix: Always alias tables when joining a table with itself

What happens if you using cross join when inner join is intended?

Result is Cartesian product, causing huge output Fix: Use INNER JOIN with ON clause to restrict matches

What happens if you assuming full outer join is supported everywhere?

Query fails on some databases like MySQL Fix: Use UNION of LEFT and RIGHT JOINs as workaround

Interview Prepdatabase-management-systemseasyAmazonGoogleMicrosoftFlipkartSwiggyRazorpay

SQL Joins - INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF

Choose your preparation mode3 modes available

Solution Trace

🎯

easyDBMSAmazonGoogleMicrosoft

Imagine you work at an e-commerce company where you need to combine customer and order data to generate reports. Understanding how different SQL joins work is crucial to get accurate results.

💡 SQL joins are fundamental to combining data from multiple tables. Beginners often struggle because each join type returns different results depending on how tables relate, and it's easy to confuse their outputs without concrete examples. Think of joins as ways to connect puzzle pieces based on matching edges (keys).

📋

Problem Statement

Given two tables, write SQL queries to demonstrate the behavior of INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN. For each join type, explain how rows from the two tables are combined and what rows appear in the result.

Tables can have up to 10^5 rowsColumns used for joining have matching data typesNULL values may appear in join columnsAssume standard SQL syntax

💡

Example

Input"Table A: {id: 1, name: 'Alice'}, {id: 2, name: 'Bob'}\nTable B: {id: 2, city: 'NY'}, {id: 3, city: 'LA'}"

Output

INNER JOIN result: {id: 2, name: 'Bob', city: 'NY'}
LEFT JOIN result: {id: 1, name: 'Alice', city: NULL}, {id: 2, name: 'Bob', city: 'NY'}
RIGHT JOIN result: {id: 2, name: 'Bob', city: 'NY'}, {id: 3, name: NULL, city: 'LA'}
FULL OUTER JOIN result: {id: 1, name: 'Alice', city: NULL}, {id: 2, name: 'Bob', city: 'NY'}, {id: 3, name: NULL, city: 'LA'}
CROSS JOIN result: all combinations of rows from A and B
SELF JOIN example: joining Table A with itself on id

INNER JOIN returns rows with matching ids; LEFT JOIN returns all from A with matches or NULLs; RIGHT JOIN returns all from B with matches or NULLs; FULL OUTER JOIN returns all rows from both with NULLs where no match; CROSS JOIN returns Cartesian product; SELF JOIN joins table with itself.

One or both tables are empty → result depends on join type
Join columns contain NULL values → affects matching rows
Multiple matching rows in one or both tables → duplicates in result
Self join with aliasing → avoid ambiguous column references

⚠️

Common Mistakes

Confusing LEFT JOIN with RIGHT JOIN

Results include unexpected NULLs or missing rows

✅ Remember LEFT JOIN keeps all rows from left table; RIGHT JOIN keeps all from right

Forgetting to alias tables in SELF JOIN

SQL error due to ambiguous column references

✅ Always alias tables when joining a table with itself

Using CROSS JOIN when INNER JOIN is intended

Result is Cartesian product, causing huge output

✅ Use INNER JOIN with ON clause to restrict matches

Assuming FULL OUTER JOIN is supported everywhere

Query fails on some databases like MySQL

✅ Use UNION of LEFT and RIGHT JOINs as workaround

Not handling NULLs in join columns

Rows with NULL join keys are excluded unexpectedly

✅ Be aware NULLs do not match any value; use IS NULL checks if needed

🧠

Brute Force (Nested Loop Join Simulation)

💡 This approach simulates how joins work internally by checking every pair of rows. It helps beginners understand the fundamental mechanics behind joins before using SQL syntax. Imagine checking every possible pair of customers and orders to find matches.

Intuition

For each row in the first table, compare it with every row in the second table to find matching rows based on join conditions.

Algorithm

Iterate over each row in Table A
For each row in Table A, iterate over each row in Table B
Check if the join condition is satisfied
Depending on join type, decide whether to include the combined row or handle unmatched rows

💡 This nested iteration is straightforward but inefficient; it reveals the core logic behind all join types.

</>

Code

def nested_loop_join(table_a, table_b, join_type='inner'):
    result = []
    matched_b_indices = set()
    for i, row_a in enumerate(table_a):
        matched = False
        for j, row_b in enumerate(table_b):
            if row_a['id'] == row_b['id']:
                matched = True
                matched_b_indices.add(j)
                combined = {**row_a, **row_b}
                result.append(combined)
        if not matched and join_type in ('left', 'full'):
            combined = {**row_a}
            for key in table_b[0].keys():
                if key != 'id':
                    combined[key] = None
            result.append(combined)
    if join_type in ('right', 'full'):
        for j, row_b in enumerate(table_b):
            if j not in matched_b_indices:
                combined = {**row_b}
                for key in table_a[0].keys():
                    if key != 'id':
                        combined[key] = None
                result.append(combined)
    return result

# Example usage
if __name__ == '__main__':
    A = [{'id':1, 'name':'Alice'}, {'id':2, 'name':'Bob'}]
    B = [{'id':2, 'city':'NY'}, {'id':3, 'city':'LA'}]
    print('INNER JOIN:', nested_loop_join(A, B, 'inner'))
    print('LEFT JOIN:', nested_loop_join(A, B, 'left'))
    print('RIGHT JOIN:', nested_loop_join(A, B, 'right'))
    print('FULL OUTER JOIN:', nested_loop_join(A, B, 'full'))

Line Notes

def nested_loop_join(table_a, table_b, join_type='inner')Defines a function to simulate joins with a parameter for join type

for i, row_a in enumerate(table_a):Outer loop iterates over each row in first table

for j, row_b in enumerate(table_b):Inner loop iterates over each row in second table to find matches

if row_a['id'] == row_b['id']Checks join condition on 'id' column

matched_b_indices.add(j)Tracks matched rows in second table for right/full joins

if not matched and join_type in ('left', 'full')Handles unmatched rows from first table for left/full joins by adding NULLs

return resultReturns the combined result list

import java.util.*;
public class NestedLoopJoin {
    public static List<Map<String, Object>> nestedLoopJoin(List<Map<String, Object>> tableA, List<Map<String, Object>> tableB, String joinType) {
        List<Map<String, Object>> result = new ArrayList<>();
        Set<Integer> matchedBIndices = new HashSet<>();
        for (int i = 0; i < tableA.size(); i++) {
            Map<String, Object> rowA = tableA.get(i);
            boolean matched = false;
            for (int j = 0; j < tableB.size(); j++) {
                Map<String, Object> rowB = tableB.get(j);
                if (rowA.get("id").equals(rowB.get("id"))) {
                    matched = true;
                    matchedBIndices.add(j);
                    Map<String, Object> combined = new HashMap<>();
                    combined.putAll(rowA);
                    combined.putAll(rowB);
                    result.add(combined);
                }
            }
            if (!matched && (joinType.equals("left") || joinType.equals("full"))) {
                Map<String, Object> combined = new HashMap<>(rowA);
                for (String key : tableB.get(0).keySet()) {
                    if (!key.equals("id")) {
                        combined.put(key, null);
                    }
                }
                result.add(combined);
            }
        }
        if (joinType.equals("right") || joinType.equals("full")) {
            for (int j = 0; j < tableB.size(); j++) {
                if (!matchedBIndices.contains(j)) {
                    Map<String, Object> rowB = tableB.get(j);
                    Map<String, Object> combined = new HashMap<>(rowB);
                    for (String key : tableA.get(0).keySet()) {
                        if (!key.equals("id")) {
                            combined.put(key, null);
                        }
                    }
                    result.add(combined);
                }
            }
        }
        return result;
    }

    public static void main(String[] args) {
        List<Map<String, Object>> A = new ArrayList<>();
        A.add(new HashMap<>() {{ put("id", 1); put("name", "Alice"); }});
        A.add(new HashMap<>() {{ put("id", 2); put("name", "Bob"); }});
        List<Map<String, Object>> B = new ArrayList<>();
        B.add(new HashMap<>() {{ put("id", 2); put("city", "NY"); }});
        B.add(new HashMap<>() {{ put("id", 3); put("city", "LA"); }});

        System.out.println("INNER JOIN: " + nestedLoopJoin(A, B, "inner"));
        System.out.println("LEFT JOIN: " + nestedLoopJoin(A, B, "left"));
        System.out.println("RIGHT JOIN: " + nestedLoopJoin(A, B, "right"));
        System.out.println("FULL OUTER JOIN: " + nestedLoopJoin(A, B, "full"));
    }
}

Line Notes

public static List<Map<String, Object>> nestedLoopJoinDefines method to simulate joins with join type parameter

for (int i = 0; i < tableA.size(); i++)Outer loop over first table rows

for (int j = 0; j < tableB.size(); j++)Inner loop over second table rows

if (rowA.get("id").equals(rowB.get("id")))Checks join condition on 'id'

matchedBIndices.add(j)Records matched indices in second table

if (!matched && (joinType.equals("left") || joinType.equals("full")))Handles unmatched rows from first table

if (joinType.equals("right") || joinType.equals("full"))Handles unmatched rows from second table

return result;Returns combined join result

#include <iostream>
#include <vector>
#include <unordered_map>
#include <string>
#include <set>
using namespace std;

using Row = unordered_map<string, string>;
using Table = vector<Row>;

Table nestedLoopJoin(const Table& tableA, const Table& tableB, const string& joinType) {
    Table result;
    set<int> matchedBIndices;
    for (const auto& rowA : tableA) {
        bool matched = false;
        for (int j = 0; j < (int)tableB.size(); j++) {
            const auto& rowB = tableB[j];
            if (rowA.at("id") == rowB.at("id")) {
                matched = true;
                matchedBIndices.insert(j);
                Row combined = rowA;
                for (const auto& [k, v] : rowB) {
                    combined[k] = v;
                }
                result.push_back(combined);
            }
        }
        if (!matched && (joinType == "left" || joinType == "full")) {
            Row combined = rowA;
            for (const auto& [k, v] : tableB[0]) {
                if (k != "id") {
                    combined[k] = "";  // Use empty string to represent NULL
                }
            }
            result.push_back(combined);
        }
    }
    if (joinType == "right" || joinType == "full") {
        for (int j = 0; j < (int)tableB.size(); j++) {
            if (matchedBIndices.find(j) == matchedBIndices.end()) {
                Row combined = tableB[j];
                for (const auto& [k, v] : tableA[0]) {
                    if (k != "id") {
                        combined[k] = "";  // Use empty string to represent NULL
                    }
                }
                result.push_back(combined);
            }
        }
    }
    return result;
}

int main() {
    Table A = {{{"id", "1"}, {"name", "Alice"}}, {{"id", "2"}, {"name", "Bob"}}};
    Table B = {{{"id", "2"}, {"city", "NY"}}, {{"id", "3"}, {"city", "LA"}}};

    auto inner = nestedLoopJoin(A, B, "inner");
    cout << "INNER JOIN:" << endl;
    for (auto& row : inner) {
        for (auto& [k, v] : row) cout << k << ":" << v << " ";
        cout << endl;
    }
    return 0;
}

Line Notes

Table nestedLoopJoin(const Table& tableA, const Table& tableB, const string& joinType)Function simulating join with join type parameter

for (const auto& rowA : tableA)Outer loop over first table rows

for (int j = 0; j < (int)tableB.size(); j++)Inner loop over second table rows

if (rowA.at("id") == rowB.at("id"))Checks join condition on 'id'

matchedBIndices.insert(j)Tracks matched rows in second table

if (!matched && (joinType == "left" || joinType == "full"))Handles unmatched rows from first table

if (joinType == "right" || joinType == "full")Handles unmatched rows from second table

return result;Returns combined join result

function nestedLoopJoin(tableA, tableB, joinType = 'inner') {
    const result = [];
    const matchedBIndices = new Set();
    for (const rowA of tableA) {
        let matched = false;
        for (let j = 0; j < tableB.length; j++) {
            const rowB = tableB[j];
            if (rowA.id === rowB.id) {
                matched = true;
                matchedBIndices.add(j);
                result.push({...rowA, ...rowB});
            }
        }
        if (!matched && (joinType === 'left' || joinType === 'full')) {
            const combined = {...rowA};
            for (const key in tableB[0]) {
                if (key !== 'id') combined[key] = null;
            }
            result.push(combined);
        }
    }
    if (joinType === 'right' || joinType === 'full') {
        for (let j = 0; j < tableB.length; j++) {
            if (!matchedBIndices.has(j)) {
                const combined = {...tableB[j]};
                for (const key in tableA[0]) {
                    if (key !== 'id') combined[key] = null;
                }
                result.push(combined);
            }
        }
    }
    return result;
}

// Example usage
const A = [{id:1, name:'Alice'}, {id:2, name:'Bob'}];
const B = [{id:2, city:'NY'}, {id:3, city:'LA'}];
console.log('INNER JOIN:', nestedLoopJoin(A, B, 'inner'));
console.log('LEFT JOIN:', nestedLoopJoin(A, B, 'left'));
console.log('RIGHT JOIN:', nestedLoopJoin(A, B, 'right'));
console.log('FULL OUTER JOIN:', nestedLoopJoin(A, B, 'full'));

Line Notes

function nestedLoopJoin(tableA, tableB, joinType = 'inner')Defines function to simulate joins with join type parameter

for (const rowA of tableA)Outer loop over first table rows

for (let j = 0; j < tableB.length; j++)Inner loop over second table rows

if (rowA.id === rowB.id)Checks join condition on 'id'

matchedBIndices.add(j)Tracks matched rows in second table

if (!matched && (joinType === 'left' || joinType === 'full'))Handles unmatched rows from first table

if (joinType === 'right' || joinType === 'full')Handles unmatched rows from second table

return result;Returns combined join result

⏱

Complexity

TimeO(n*m) where n and m are sizes of the two tables

SpaceO(n*m) in worst case for storing results

Every row in table A is compared with every row in table B, leading to quadratic time complexity

💡 For 1000 rows in each table, this means 1,000,000 comparisons, which is inefficient for large data

Interview Verdict: Use only to introduce join concepts; not practical for large datasets

This approach is too slow for real databases but is invaluable for understanding join mechanics

Approach	Time	Space	Stack Risk	Reconstruct	Use In Interview
1. Brute Force (Nested Loop)	`O(n*m)`	`O(n*m)`	No	N/A	Mention only - never code for large data
2. Hash Join (Hash Map Based)	`O(n + m)`	`O(min(n, m))`	No	N/A	Code if asked for efficient join simulation
3. SQL Query Syntax	`O(1) for writing queries`	`O(1)`	No	N/A	Must know and write fluently

Practice

(1/5)

1. You have a large table frequently queried by range conditions on a date column and occasionally updated. Which indexing strategy is most appropriate to optimize query performance?

easy

A. Use a clustered index on the date column to store rows physically sorted by date.

B. Use a non-clustered index on the date column to avoid physical reordering on updates.

C. Use no index and rely on full table scans since updates are frequent.

D. Use a clustered index on a unique ID column and a non-clustered index on the date column.

Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard

Intuition

Algorithm

Intuition

Algorithm

Intuition

Algorithm

How to Present

Time Allocation

What the Interviewer Tests

Common Follow-ups

When to Use

Signature Phrases

NOT This Pattern When

Similar Problems

Practice

Solution

Step 1: Understand query pattern for range scans on date

Step 2: Consider clustered index effect on physical ordering

Step 3: Evaluate update impact tradeoff

Step 4: Analyze other options

Final Answer:

Quick Check:

Solution

Step 1: Understand lossless join vs dependency preservation

Step 2: Analyze the trade-off

Step 3: Scenario matching

Final Answer:

Quick Check:

Solution

Step 1: Identify the requirement for explicit invocation and return value

Step 2: Exclude triggers and stored procedures

Step 3: Exclude views

Final Answer:

Quick Check:

Solution

Step 1: Review Armstrong's Axioms

Step 2: Analyze each statement

Final Answer:

Quick Check:

Solution

Step 1: Understand MVCC version management

Step 2: Analyze locking overhead

Step 3: Analyze isolation guarantees

Step 4: Analyze write amplification

Final Answer:

Quick Check:

Ready to prove it?