Purpose of This Lesson

SQL JOIN questions are common in data-engineering assessments because they test whether you can predict which rows appear when combining related tables. This lesson focuses on choosing the correct JOIN type based on whether unmatched rows should be retained.

Key Concepts

• Join key: The column or columns used to relate two tables, such as customerNumber.
• Inner join: Returns only rows with matching join keys in both tables. JOIN without a qualifier means INNER JOIN.
• Left outer join: Returns every row from the left table and matching rows from the right table. Unmatched right-side columns become NULL.
• Right outer join: Returns every row from the right table and matching rows from the left table. Unmatched left-side columns become NULL.
• Full outer join: Returns all rows from both tables. Matching rows are combined; unmatched portions contain NULL values.
• Cross join: Returns every possible combination of rows from the two tables, regardless of matching keys. This is also called a Cartesian product.
• Table aliases: Short names such as c and p make queries easier to read and allow columns to be qualified unambiguously.

SQL JOIN Behavior and Row Preservation

Assume two tables, customers and payments, related by customerNumber:

SELECT c.customerName, p.paymentDate, p.amount
FROM customers AS c
INNER JOIN payments AS p
  ON c.customerNumber = p.customerNumber;

This query returns customer and payment details only when the customer number exists in both tables. Customers without payments are excluded, as are payment records whose customer number has no corresponding customer row.

Left Join

SELECT c.customerName, p.paymentDate, p.amount
FROM customers AS c
LEFT JOIN payments AS p
  ON c.customerNumber = p.customerNumber;

The customers table is on the left, so every customer is retained. If a customer has no matching payment, p.paymentDate and p.amount are NULL.

Right Join

A right join reverses the preservation rule: every row from the table on the right is retained, while unmatched columns from the left table become NULL. Rewriting a right join as a left join by switching table order is often easier to reason about.

Full Outer Join

A full outer join preserves all rows from both tables. It is useful when the objective is to identify mismatched keys, such as customers with no payments and payments that do not map to a known customer.

Cross Join

SELECT *
FROM customers
CROSS JOIN payments;

A cross join produces one output row for every customer-payment pair. If the first table has m rows and the second has n rows, the result has m × n combinations before any additional filtering. The transcript refers to this as a “cross outer join,” but the standard SQL term is cross join; it is not an outer join in the same sense as left, right, or full outer join.

Exam- or Assessment-Relevant Takeaways

• If a question says only JOIN, interpret it as an inner join.
• Look for wording such as “only matching records” or “intersection”; that indicates an inner join.
• “Keep all customers, even those without payments” means customers must be the left table in a left join.
• “Keep all payment records, even those without a customer” means preserve the payments table with a right join or place it on the left and use a left join.
• “Show every record from both sources, including unmatched records” indicates a full outer join.
• NULL values in columns from the non-preserved side are expected for unmatched rows.
• A cross join is not a matching operation. It intentionally creates every pair and can produce a very large result.
• Table aliases do not change join behavior; they only provide shorter references such as c.customerNumber and p.customerNumber.

Tool / Feature Decision Guide

Requirement	Appropriate JOIN	Result behavior
Return only related records	INNER JOIN or unqualified JOIN	Keeps the intersection of the tables
Keep every row from the first table	LEFT JOIN	Unmatched right-side values are NULL
Keep every row from the second table	RIGHT JOIN	Unmatched left-side values are NULL
Audit both sources for missing relationships	FULL OUTER JOIN	Keeps unmatched rows from both sides
Generate every possible pair	CROSS JOIN	Produces a Cartesian product; use cautiously

Common Traps / Misconceptions

• Assuming JOIN means a full combination: An unqualified JOIN means inner join, not cross join.
• Putting the preserved table on the wrong side: In a left join, the left table is the one whose rows are guaranteed to remain.
• Treating NULL as a matching value: A NULL in right-side columns after a left join normally indicates that no matching right-side row was found; it is not payment data.
• Confusing full outer join with cross join: A full outer join follows the relationship and adds unmatched rows. A cross join ignores the relationship and creates every pair.
• Forgetting that aliases qualify columns: Once aliases are assigned, c.customerNumber refers to the customers table and p.customerNumber refers to the payments table.
• Underestimating cross-join size: Combining two large tables this way can rapidly create an impractical result set.

Real-World Engineer / Analyst Notes

• Start by stating which table must be preserved. That decision usually determines whether the query should use a left, right, or full outer join.
• A left join is often easier to read than a right join: place the table whose rows matter most on the left and preserve it with LEFT JOIN.
• Use a full outer join as a reconciliation or troubleshooting technique when investigating keys that exist in one source but not the other.
• Inspect unmatched rows and their NULL columns separately from matched results so that missing relationships are not mistaken for missing attribute values.
• Before running a cross join, estimate the potential row count as the product of the input row counts and confirm that the all-pairs result is genuinely required.

Quick Reference Summary

• JOIN = INNER JOIN by default.
• Inner join: matching rows only.
• Left join: all left rows plus matching right rows.
• Right join: all right rows plus matching left rows.
• Full outer join: all rows from both tables, with NULLs where no match exists.
• Cross join: every possible row combination; potentially very large.
• The join condition normally compares corresponding keys, for example c.customerNumber = p.customerNumber.

Flashcards

Q: A report should include every customer, including customers who have never made a payment. Which join strategy should you choose?

A: Put customers on the left and use LEFT JOIN payments. Customers without matches remain, with payment columns set to NULL.

Q: What does an unqualified JOIN mean in SQL?

A: It means INNER JOIN, which returns only rows whose join condition matches in both tables.

Q: When would a full outer join be more appropriate than an inner join?

A: Use a full outer join when you need all rows from both sources, including records with keys missing from the other source. This is especially useful for reconciliation and mismatch analysis.

Q: A payment system contains records whose customer IDs are not present in customers. Which join preserves those payment records?

A: Preserve the payments table with a right join, or place payments on the left and use a left join. The decisive requirement is retaining unmatched payment rows.

Q: How do inner and left joins differ when a left-table row has no match?

A: An inner join removes that row. A left join retains it and supplies NULL for selected columns from the right table.

Q: What result does a cross join produce?

A: It produces every possible combination of rows from the two tables, regardless of whether their keys match.

Q: Two tables have 500 and 2,000 rows. What is the maximum basic row count from a cross join?

A: The Cartesian product contains 1,000,000 combinations, calculated as 500 × 2,000, before any additional filtering.

Q: In FROM customers AS c INNER JOIN payments AS p, what do c and p represent?

A: They are table aliases. c.customerNumber refers to the customers table, while p.customerNumber refers to the payments table.

Q: Which join is best for finding keys that occur in one table but not the other?

A: A full outer join is a useful starting point because it retains unmatched rows from both tables, allowing the missing side to be identified through NULL values.

Q: What is the difference between a right join and a left join in terms of row preservation?

A: A right join preserves every row from the right table; a left join preserves every row from the left table. They can often express the same logic by reversing table order.

Q: What does NULL in payment columns typically indicate after a left join from customers to payments?

A: It indicates that the customer row had no matching payment row under the join condition. It does not necessarily mean that a payment attribute itself was stored as missing in an existing payment row.

Q: Why should a cross join be treated cautiously in production work?

A: Its output grows as the product of the two input sizes and can become extremely large. It should be used only when all row combinations are intentional.

Practice Questions

Question 1

An analyst needs a list of all customers and any payment information available for each one. Customers with no payments must still appear. Which query structure is correct?

A. customers INNER JOIN payments
B. customers LEFT JOIN payments
C. customers CROSS JOIN payments
D. customers FULL OUTER JOIN payments, with no need to consider table preservation

Correct answer: B

Explanation: The customers table must be preserved, so it belongs on the left side of a left join. Unmatched payment columns will be NULL.

Question 2

A data engineer is reconciling customer IDs across customer and payment sources and wants to see records that exist in either source, including IDs missing from the other source. Which join is most suitable?

A. Inner join
B. Left join from customers only
C. Full outer join
D. Cross join

Correct answer: C

Explanation: A full outer join retains unmatched rows from both tables, making it appropriate for identifying discrepancies in either direction.

Question 3

A query uses FROM customers c JOIN payments p ON c.customerNumber = p.customerNumber. Which rows should the engineer expect?

A. Every customer, whether or not a payment exists
B. Every payment, whether or not a customer exists
C. Only customer-payment records with matching customer numbers
D. Every possible customer-payment combination

Correct answer: C

Explanation: An unqualified JOIN is an inner join, so only the intersection defined by the join condition is returned.

Question 4

An engineer accidentally uses CROSS JOIN between two large production tables and observes a rapidly expanding result. What explains the behavior?

A. Cross joins retain only unmatched rows
B. Cross joins return every possible pair of rows
C. Cross joins automatically duplicate only rows with equal keys
D. Cross joins are equivalent to inner joins

Correct answer: B

Explanation: A cross join creates a Cartesian product. With m and n input rows, it can produce m × n output rows.

WordPress Metadata

Suggested Slug:
sql-join-types-inner-left-right-full-cross

Meta Description:
Learn how SQL inner, left, right, full outer, and cross joins determine matched rows, unmatched rows, NULL values, and Cartesian-product size.

Tags:
AWS Certified Data Engineer, SQL, SQL joins, inner join, left join, right join, full outer join, cross join, data reconciliation, table aliases, data engineering