JOINS - FULL, CROSS JOIN

This lesson on JOINS - FULL, CROSS JOIN is hands-on and example-driven. You will be able to combine data sets using various SQL JOIN types (INNER, LEFT, RIGHT, FULL, CROSS) and vertical stacking methods (UNION/UNION ALL). You will select the correct join type to manage non-matching rows and predict the resulting data structure and size accurately.

What You'll Be Able To Do

• Distinguish the result sets produced by INNER JOIN versus FULL JOIN.
• Explain how NULL values are introduced when using LEFT or RIGHT joins.
• Rewrite a RIGHT JOIN query using equivalent LEFT JOIN syntax and table order.
• Predict the exact output size and structure resulting from a CROSS JOIN.
• Define the functional difference between a Primary Key and a Foreign Key in schema design.
• Select the appropriate vertical stacking method based on requirements for duplicate removal.

Detailed Concept Walkthrough

1. Full Outer Join

A FULL JOIN combines all rows from both tables, regardless of whether a match exists in the other table. It provides a complete, merged view of both datasets.

• Mechanism: Rows that match on the join condition are combined into a single result row.
• Execution Flow: If a row in Table A has no match in Table B, the columns belonging to Table B are filled with NULL values.
• Under the Hood: If a row in Table B has no match in Table A, the columns belonging to Table A are similarly filled with NULL values.
• Syntax Rule: Requires the FULL JOIN or FULL OUTER JOIN keywords followed by the mandatory ON clause.

SELECT *
FROM Customers C
FULL JOIN Orders O
  ON C.customer_id = O.customer_id;

Key Takeaway: A FULL JOIN ensures zero data loss from either source table, maximizing data visibility.

2. Cross Join (Cartesian Product)

A CROSS JOIN pairs every row of the first table with every row of the second table. This generates a Cartesian product, useful for creating combinations or permutations.

• Mechanism: If Table A has M rows and Table B has N rows, the resulting dataset will contain M * N rows.
• Execution Flow: No join condition (ON clause) is required or permitted, as the join happens implicitly by pairing everything.
• Best Practice: Use with extreme caution on large tables as the resulting dataset size grows exponentially and can crash systems.
• Syntax Rule: Use the CROSS JOIN keyword directly between the table names in the FROM clause.

SELECT P.name, C.color
FROM Products P
CROSS JOIN Colors C;

Key Takeaway: A CROSS JOIN generates all possible pairings between the two datasets without requiring a relationship key.

3. Vertical Stacking (UNION)

UNION and UNION ALL stack the result sets of two separate SELECT statements vertically, combining them into a single column structure.

• Mechanism: Both operations require that the SELECT statements have the same number of columns and compatible data types in the same order.
• Execution Flow: UNION performs an internal sort and comparison step to identify and discard duplicate rows from the combined result set.
• Under the Hood: UNION ALL skips the costly deduplication step, making it significantly faster when duplicates are acceptable or known not to exist.
• Best Practice: Always use UNION ALL unless the business requirement strictly demands the removal of identical records.

SELECT customer_id FROM TableA
UNION ALL
SELECT customer_id FROM TableB;

Key Takeaway: UNION removes duplicates; UNION ALL retains duplicates and is generally faster.

Topics Covered in JOINS - FULL, CROSS JOIN

1. Primary Key Defined (0:48 - 1:01) — Defines the column(s) that uniquely identify a row within a table.
2. Foreign Key Defined (1:02 - 1:17) — Defines a column linking to another table's primary key to establish a relationship.
3. Inner Join Review (2:39 - 4:59) — Combines rows only where a match exists in both tables based on the join condition.
4. Left Join Review (5:00 - 5:46) — Includes all rows from the first table, filling NULLs for non-matching rows from the right table.
5. Full Join Explained (7:07 - 8:08) — Combines all data from both tables, filling NULLs where no match exists in either direction.
6. Union Explained (8:09 - 8:45) — Stacks datasets vertically while performing a deduplication step on overlapping records.
7. Union All Explained (8:46 - 8:59) — Stacks datasets vertically without performing the costly duplicate removal step.
8. Cross Join Explained (9:00 - 9:21) — Joins every row of the first table with every row of the second table, creating a Cartesian product.

SQL Cheat Sheet

• FULL JOIN — Returns all rows from both tables, filling NULLs

  T1 FULL JOIN T2 ON T1.id = T2.id
  

• CROSS JOIN — Joins every row of T1 with every row of T2

  SELECT * FROM T1 CROSS JOIN T2
  

• UNION — Stacks data vertically, removing duplicate rows

  SELECT col FROM T1 UNION SELECT col FROM T2
  

• UNION ALL — Stacks data vertically, retaining all duplicate rows

  SELECT col FROM T1 UNION ALL SELECT col FROM T2
  

• INNER JOIN — Returns only rows that have matches in both tables

  T1 JOIN T2 ON T1.id = T2.id
  

• Primary Key — Uniquely identifies a row within its table

• Foreign Key — Establishes relationship to another table's PK

Comparison Table

Common Pitfalls

• Mistake: Using CROSS JOIN on large tables. Avoid: Always check table sizes before executing a CROSS JOIN.
• Mistake: Assuming JOIN means FULL JOIN. Avoid: JOIN is shorthand for INNER JOIN; use FULL JOIN explicitly.
• Mistake: Using UNION when performance is critical. Avoid: Use UNION ALL to avoid the costly deduplication step.
• Mistake: Confusing LEFT JOIN and RIGHT JOIN results. Avoid: Always define the table you want to keep entirely as the 'left' table.

FAQs

• Why is RIGHT JOIN rarely used in practice? Any RIGHT JOIN can be rewritten as a LEFT JOIN simply by swapping the order of the tables in the FROM clause. LEFT JOIN is the industry standard.
• What happens if I use FULL JOIN without an ON clause? SQL will typically throw an error because FULL JOIN requires a condition to attempt matching rows and determine where NULLs should be placed.
• How does UNION deduplicate records? The database compares every column of the combined rows to identify and discard exact duplicates before returning the final result set.
• Can I use CROSS JOIN to simulate an INNER JOIN? Yes, by using a WHERE clause after the CROSS JOIN to filter the Cartesian product based on the desired join condition.