Skip to main content

πŸ”— SQL JOINs


1️⃣ Table Definitions & Sample Data​

πŸ‘€ Table: customers​

CREATE OR REPLACE TABLE customers (
customer_id INT,
name STRING,
city STRING
);

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Arjun Mehta', 'Chennai'),
(2, 'Priya Verma', 'Mumbai'),
(3, 'Rahul Singh', 'Delhi'),
(4, 'Sneha Iyer', 'Bangalore'),
(5, 'Kiran Rao', 'Hyderabad'),
(6, 'Anjali Sharma', 'Kolkata'),
(7, 'Vikram Desai', 'Pune'),
(8, 'Neha Reddy', 'Chennai'),
(9, 'Suresh Das', 'Mumbai'),
(10, 'Lakshmi Nair', 'Bangalore'),
(11, 'Ajay Kumar', 'Delhi'),
(12, 'Meena Joshi', 'Ahmedabad');

πŸ“¦ Table: orders​

CREATE OR REPLACE TABLE orders (
order_id INT,
customer_id INT,
amount INT
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
(101, 1, 500),
(102, 2, 700),
(103, 2, 300),
(104, 4, 800),
(105, 5, 600),
(106, 7, 450),
(107, 8, 650),
(108, 11, 550),
(109, 13, 900), -- unmatched
(110, 6, 750),
(111, 14, 680), -- unmatched
(112, 2, 320);

2️⃣ JOIN Types Explained​

πŸ”— INNER JOIN - ( Finding the Perfect Match )​

  • βœ… Returns rows when there is a match in both tables.
  • ❌ Excludes non-matching records.
    • INNER JOIN is the most commonly used JOIN type and represents the intersection between two tables – returning only records that have matching values in both tables.

Snowflake Login Screen

SQL:

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Output:

nameorder_idamount
Arjun Mehta101500
Priya Verma102700
Priya Verma103300
Priya Verma112320
Sneha Iyer104800
Kiran Rao105600
Anjali Sharma110750
Vikram Desai106450
Neha Reddy107650
Ajay Kumar108550

👸 LEFT JOIN ( also called LEFT OUTER JOIN) : Keep Everything from Table A​

  • βœ… All records from the left table (customers).
  • πŸ” Matched data from right table (orders), NULL where not matched.

Snowflake Login Screen

SQL:

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Output:

nameorder_idamount
Arjun Mehta101500
Priya Verma102700
Priya Verma103300
Priya Verma112320
Rahul SinghNULLNULL
Sneha Iyer104800
Kiran Rao105600
Anjali Sharma110750
Vikram Desai106450
Neha Reddy107650
Suresh DasNULLNULL
Lakshmi NairNULLNULL
Ajay Kumar108550
Meena JoshiNULLNULL

👺 RIGHT JOIN ( or RIGHT OUTER JOIN ) : Prioritize Table B​

  • βœ… All records from the right table (orders).
  • πŸ” Matched data from left table (customers), NULL where not matched.

Snowflake Login Screen

SQL:

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Output:

nameorder_idamount
Arjun Mehta101500
Priya Verma102700
Priya Verma103300
Priya Verma112320
Sneha Iyer104800
Kiran Rao105600
Vikram Desai106450
Neha Reddy107650
Ajay Kumar108550
NULL109900
Anjali Sharma110750
NULL111680

πŸ” FULL OUTER JOIN​

  • βœ… All records from both tables.
  • πŸ” Matched where possible; unmatched will show NULL.
    • FULL OUTER JOIN combines the results of both LEFT and RIGHT JOINs, returning all records from both tables with matching records from opposite tables where available. When no match exists, NULL values appear in the columns from the table without a match.

Snowflake Login Screen

SQL:

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Output:

nameorder_idamount
Arjun Mehta101500
Priya Verma102700
Priya Verma103300
Priya Verma112320
Rahul SinghNULLNULL
Sneha Iyer104800
Kiran Rao105600
Anjali Sharma110750
Vikram Desai106450
Neha Reddy107650
Suresh DasNULLNULL
Lakshmi NairNULLNULL
Ajay Kumar108550
Meena JoshiNULLNULL
NULL109900
NULL111680

πŸ”„ CROSS JOIN​

  • βœ… Cartesian product of both tables.
  • 🧨 Very large output: M x N rows.
    • Unlike the previous JOINs, CROSS JOIN doesn’t use a joining condition. Instead, it creates a Cartesian product, generating all possible combinations by pairing each row from the first table with every row from the second table.

Snowflake Login Screen

SQL:

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Output Example:

nameorder_id
Arjun Mehta101
Arjun Mehta102
......
Meena Joshi112

(Total rows: 12 customers Γ— 12 orders = 144 rows)


πŸ‘―β€β™‚οΈ SELF JOIN – Same Table Comparison​

  • A SELF JOIN occurs when a table joins with itself, treating the same table as if it were two separate tables. This requires using table aliases to distinguish between the two instances of the same table.
  • A table joins with itself to compare rows.**

Snowflake Login Screen

πŸ‘₯ Table: employees​

πŸ“„ DDL (Create Table)​

CREATE OR REPLACE TABLE employees (
emp_id INT,
emp_name STRING,
manager_id INT
);

🧾 Insert Sample Data​

INSERT INTO employees (emp_id, emp_name, manager_id) VALUES
(1, 'Anil', NULL), -- CEO
(2, 'Bhavna', 1), -- Reports to Anil
(3, 'Chirag', 1), -- Reports to Anil
(4, 'Divya', 2), -- Reports to Bhavna
(5, 'Eshan', 2), -- Reports to Bhavna
(6, 'Farah', 3), -- Reports to Chirag
(7, 'Gaurav', 3); -- Reports to Chirag

πŸ”„ Self JOIN Query (Employee–Manager Relationship)​

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

πŸ“Š Expected Output​

employeemanager
AnilNULL
BhavnaAnil
ChiragAnil
DivyaBhavna
EshanBhavna
FarahChirag
GauravChirag

🌳 Organizational Hierarchy – Tree View​

Anil (CEO)
β”œβ”€β”€ Bhavna
β”‚ β”œβ”€β”€ Divya
β”‚ └── Eshan
└── Chirag
β”œβ”€β”€ Farah
└── Gaurav

πŸ”Ž How to Read This?​

  • Anil is at the top level (no manager).
  • Bhavna and Chirag report to Anil.
  • Divya and Eshan report to Bhavna.
  • Farah and Gaurav report to Chirag.

βœ… Summary​

This tree helps visualize the employee-manager hierarchy in an intuitive, human-readable way. In real-world reporting or dashboards, this kind of hierarchy is useful for:

  • Organizational charts
  • Reporting chains
  • Escalation paths
  • HR analytics

🧠 Why Use a SELF JOIN Here?​

  • βœ… Useful for hierarchy reporting (e.g., Org Chart).
  • βœ… Relate each row to another row in the same table.
  • βœ… Easy way to find reporting lines (manager–employee structure).

βœ… Summary of JOIN Types​

JOIN TypeDescription
INNER JOINOnly matching rows in both tables
LEFT JOINAll rows from left, matched from right or NULL
RIGHT JOINAll rows from right, matched from left or NULL
FULL OUTER JOINAll rows from both, matched or unmatched
CROSS JOINEvery combination of left and right

πŸ“ Takeaways​

  • βž• Need matching records from both tables? β†’ Use INNER JOIN
  • ➑️ Need all records from Table A? β†’ Use LEFT JOIN
  • ⬅️ Need all records from Table B? β†’ Use RIGHT JOIN
  • 🌐 Need everything regardless of match? β†’ Use FULL OUTER JOIN
  • πŸ”’ Need every possible combo? β†’ Use CROSS JOIN
  • πŸ” Need to compare rows within the same table? β†’ Use SELF JOIN