FULL, CROSS, SELF JOIN

FULL OUTER JOIN

The FULL OUTER JOIN returns all rows from both tables. When there's no match, NULL values are returned for the non-matching side.

Basic Syntax

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

How FULL OUTER JOIN Works

table_a                    table_b
+----+--------+           +----+--------+
| id | name   |           | id | name   |
+----+--------+           +----+--------+
| 1  | Alice  | <-------> | 1  | Alice  |
| 2  | Bob    | <-------> | 2  | Bob    |
| 3  | Charlie|    NULL   | 5  | Eve    |
| 4  | Diana  |   NULL    |    |        |
+----+--------+           +----+--------+

Result: All 6 rows
- Alice, Bob: matched
- Charlie, Diana: NULL from table_b
- Eve: NULL from table_a

FULL OUTER JOIN Examples

-- Compare customers in two databases
CREATE TABLE customers_db1 (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE customers_db2 (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO customers_db1 VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO customers_db2 VALUES (1, 'Alice'), (2, 'Robert'), (4, 'Diana');

-- Find all customers and their matches
SELECT 
    COALESCE(c1.customer_id, c2.customer_id) AS customer_id,
    COALESCE(c1.name, c2.name) AS name,
    CASE 
        WHEN c1.customer_id IS NOT NULL AND c2.customer_id IS NOT NULL THEN 'Both'
        WHEN c1.customer_id IS NOT NULL THEN 'DB1 Only'
        ELSE 'DB2 Only'
    END AS source
FROM customers_db1 c1
FULL OUTER JOIN customers_db2 c2 ON c1.customer_id = c2.customer_id;

FULL OUTER JOIN with Aggregation

-- Compare sales between two regions
SELECT 
    COALESCE(n.region, s.region) AS region,
    COALESCE(n.total_sales, 0) AS north_sales,
    COALESCE(s.total_sales, 0) AS south_sales
FROM 
    (SELECT region, SUM(amount) AS total_sales FROM sales_north GROUP BY region) n
FULL OUTER JOIN 
    (SELECT region, SUM(amount) AS total_sales FROM sales_south GROUP BY region) s
    ON n.region = s.region;

FULL OUTER JOIN Best Practices

1. Use COALESCE to handle NULLs from both sides
2. Check both sides for NULL to identify non-matches
3. Consider UNION approach for databases without FULL OUTER JOIN

-- Good: Full comparison with NULL handling
SELECT 
    COALESCE(a.id, b.id) AS id,
    COALESCE(a.name, b.name) AS name,
    a.value AS value_a,
    b.value AS value_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

FULL OUTER JOIN is useful for comparing datasets and finding differences between tables.

CROSS JOIN

The CROSS JOIN returns the Cartesian product of two tables. Every row from the first table is combined with every row from the second table.

Basic Syntax

SELECT columns
FROM table1
CROSS JOIN table2;
-- or implicit syntax:
SELECT columns
FROM table1, table2;

How CROSS JOIN Works

table_a (3 rows)          table_b (2 rows)
+----+--------+           +----+--------+
| id | name   |           | id | color  |
+----+--------+           +----+--------+
| 1  | Alice  |           | 1  | Red    |
| 2  | Bob    |           | 2  | Blue   |
| 3  | Charlie|           +----+--------+
+----+--------+

Result: 3 × 2 = 6 rows
(1,Alice,1,Red), (1,Alice,2,Blue),
(2,Bob,1,Red), (2,Bob,2,Blue),
(3,Charlie,1,Red), (3,Charlie,2,Blue)

CROSS JOIN Examples

-- Generate all combinations of sizes and colors
CREATE TABLE sizes (
    size_id INT PRIMARY KEY,
    size_name VARCHAR(10)
);

CREATE TABLE colors (
    color_id INT PRIMARY KEY,
    color_name VARCHAR(20)
);

INSERT INTO sizes VALUES (1, 'S'), (2, 'M'), (3, 'L');
INSERT INTO colors VALUES (1, 'Red'), (2, 'Blue');

-- All size-color combinations
SELECT 
    s.size_name,
    c.color_name
FROM sizes s
CROSS JOIN colors c;

-- Result: S-Red, S-Blue, M-Red, M-Blue, L-Red, L-Blue

CROSS JOIN with WHERE

-- Filter Cartesian product (useful for specific combinations)
SELECT 
    s.size_name,
    c.color_name,
    p.price
FROM sizes s
CROSS JOIN colors c
CROSS JOIN price_list p
WHERE s.size_name = p.size AND c.color_name = p.color;

-- Generate date series
SELECT 
    d.date_value,
    h.hour_value
FROM 
    (SELECT DATE_ADD('2024-01-01', INTERVAL n DAY) AS date_value 
     FROM numbers WHERE n < 30) d
CROSS JOIN 
    (SELECT n AS hour_value FROM numbers WHERE n < 24) h;

CROSS JOIN Performance

-- Warning: CROSS JOINs can produce huge result sets
-- 1000 rows × 1000 rows = 1,000,000 rows!

-- Always filter when possible
SELECT a.id, b.id
FROM table_a a
CROSS JOIN table_b b
WHERE a.category = b.category;  -- Reduces result set

-- Use LIMIT for testing
SELECT * FROM table_a CROSS JOIN table_b LIMIT 100;

CROSS JOIN Best Practices

1. Understand the result size - rows × rows
2. Filter early to reduce result set
3. Use for generating combinations - sizes × colors
4. Avoid on large tables without filtering

-- Good: CROSS JOIN for combinations
SELECT 
    s.size_name,
    c.color_name,
    CONCAT(s.size_name, '-', c.color_name) AS sku
FROM sizes s
CROSS JOIN colors c
ORDER BY s.size_name, c.color_name;

-- Bad: CROSS JOIN without filter on large tables
SELECT * FROM orders CROSS JOIN products;  -- Millions of rows!

CROSS JOIN is useful for generating combinations but must be used carefully to avoid performance issues.

SELF JOIN

A SELF JOIN is a join where a table is joined with itself. It's useful for hierarchical data, finding relationships within the same table.

Basic Syntax

SELECT columns
FROM table1 t1
INNER JOIN table1 t2 ON t1.column = t2.column;

SELF JOIN Examples

-- Employee hierarchy
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'Alice', NULL),
    (2, 'Bob', 1),
    (3, 'Charlie', 1),
    (4, 'Diana', 2),
    (5, 'Eve', 2),
    (6, 'Frank', 3);

-- Find each employee's manager
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Find employees who report to the same manager
SELECT 
    e1.name AS employee1,
    e2.name AS employee2,
    m.name AS manager
FROM employees e1
INNER JOIN employees e2 
    ON e1.manager_id = e2.manager_id
    AND e1.emp_id < e2.emp_id
LEFT JOIN employees m ON e1.manager_id = m.emp_id;

SELF JOIN for Comparisons

-- Find products with similar prices
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

SELECT 
    p1.name AS product1,
    p2.name AS product2,
    ABS(p1.price - p2.price) AS price_diff
FROM products p1
INNER JOIN products p2 
    ON p1.product_id < p2.product_id
    AND ABS(p1.price - p2.price) < 10;

-- Find consecutive dates
CREATE TABLE daily_sales (
    sale_date DATE PRIMARY KEY,
    amount DECIMAL(10,2)
);

SELECT 
    d1.sale_date,
    d1.amount,
    d2.amount AS prev_day_amount,
    d1.amount - d2.amount AS change
FROM daily_sales d1
INNER JOIN daily_sales d2 
    ON d1.sale_date = DATE_ADD(d2.sale_date, INTERVAL 1 DAY);

SELF JOIN for Hierarchical Queries

-- Find all subordinates of Alice (recursive)
-- Note: Recursive CTEs are better for deep hierarchies
SELECT 
    e.name AS subordinate,
    LEVEL AS depth
FROM employees e
START WITH e.manager_id = 1
CONNECT BY PRIOR e.emp_id = e.manager_id;

-- Find the path from employee to top manager
SELECT 
    e.name,
    m1.name AS manager,
    m2.name AS grand_manager
FROM employees e
LEFT JOIN employees m1 ON e.manager_id = m1.emp_id
LEFT JOIN employees m2 ON m1.manager_id = m2.emp_id;

SELF JOIN Best Practices

1. Use table aliases - Essential for distinguishing the two instances
2. Use different aliases - t1, t2 or e, m
3. Be careful with conditions - Avoid infinite loops
4. Consider recursion for deep hierarchies

-- Good: Clear aliases for SELF JOIN
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Good: Avoid duplicates with proper condition
SELECT 
    p1.name,
    p2.name
FROM products p1
INNER JOIN products p2 
    ON p1.product_id < p2.product_id  -- Avoids (a,b) and (b,a)
    AND p1.category = p2.category;

SELF JOIN is powerful for analyzing relationships within the same table.

COALESCE for NULL Handling

COALESCE returns the first non-NULL value from a list of arguments. It's essential for handling NULLs in joins.

Basic Syntax

COALESCE(value1, value2, value3, ...)

COALESCE Examples

-- Replace NULL with default value
SELECT 
    name,
    COALESCE(phone, 'No phone') AS phone,
    COALESCE(email, 'No email') AS email
FROM customers;

-- COALESCE with joins
SELECT 
    c.name,
    COALESCE(o.order_id, 'No orders') AS order_info,
    COALESCE(o.total, 0) AS total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Multiple fallback values
SELECT 
    COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

COALESCE in FULL OUTER JOIN

-- Compare two datasets
SELECT 
    COALESCE(a.id, b.id) AS id,
    COALESCE(a.name, b.name) AS name,
    a.value AS value_a,
    b.value AS value_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

-- Find differences
SELECT 
    COALESCE(a.id, b.id) AS id,
    CASE 
        WHEN a.value IS NULL THEN 'Only in B'
        WHEN b.value IS NULL THEN 'Only in A'
        WHEN a.value != b.value THEN 'Different'
        ELSE 'Same'
    END AS status
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

COALESCE vs NVL vs IFNULL

-- COALESCE (standard SQL, works everywhere)
SELECT COALESCE(column, 'default') FROM table;

-- NVL (Oracle)
SELECT NVL(column, 'default') FROM table;

-- IFNULL (MySQL)
SELECT IFNULL(column, 'default') FROM table;

-- ISNULL (SQL Server)
SELECT ISNULL(column, 'default') FROM table;

-- Best practice: Use COALESCE for portability

COALESCE with Aggregates

-- Handle NULL aggregates
SELECT 
    c.name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent,
    COALESCE(AVG(o.total), 0) AS avg_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Conditional aggregation with COALESCE
SELECT 
    category,
    COALESCE(SUM(CASE WHEN status = 'active' THEN amount END), 0) AS active_total,
    COALESCE(SUM(CASE WHEN status = 'inactive' THEN amount END), 0) AS inactive_total
FROM products
GROUP BY category;

COALESCE Best Practices

1. Use COALESCE for portability across databases
2. Provide appropriate defaults for the data type
3. Use with LEFT/RIGHT/FULL JOINs to handle NULLs
4. Consider performance - COALESCE is generally fast

-- Good: Comprehensive NULL handling
SELECT 
    COALESCE(c.name, 'Unknown') AS customer,
    COALESCE(c.email, 'N/A') AS email,
    COUNT(o.order_id) AS orders,
    COALESCE(SUM(o.total), 0.00) AS total_spent,
    COALESCE(MAX(o.order_date), 'Never') AS last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email;

COALESCE is the most versatile and portable way to handle NULL values in SQL.