Loading auth...
Easy10 minDatabase Fundamentals
UpdatedAug 2, 2026
Edit

SQL Joins and Their Types

CONCEPTS:SQL Joins

Why This Is Asked

Understanding joins is fundamental to working with relational databases. Interviewers want to see if you know how to combine data efficiently and understand the difference between inclusive and exclusive joins.

Key Concepts

  • INNER JOIN: Intersection of sets.
  • LEFT/RIGHT JOIN: One-sided inclusion.
  • FULL JOIN: Union of sets.
  • CROSS JOIN: Cartesian product.
  • SELF JOIN: Hierarchy or comparative queries.

Answers by Technology

+ Add Variant

Expected Answer (PostgreSQL 16)

PostgreSQL supports all standard ANSI SQL join types.

  1. INNER JOIN: Returns rows only when there is a match in both tables.
  2. LEFT JOIN: Returns all rows from the left table and matched rows from the right.
  3. RIGHT JOIN: Returns all rows from the right table and matched rows from the left.
  4. FULL OUTER JOIN: Returns all rows when there is a match in either table.
  5. CROSS JOIN: Cartesian product.
  6. LATERAL JOIN: (PostgreSQL specific) Allows a subquery in the FROM clause to refer to columns of preceding FROM items.

Why It Matters

PostgreSQL’s optimizer is highly advanced. In version 16, joins on partitioned tables and parallel joins have seen significant performance improvements. Understanding join types is critical for efficient data retrieval in relational models.

SQL Example

-- Standard Join
SELECT c.name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- LATERAL JOIN (Useful for top-N per group)
SELECT c.name, latest_orders.order_date
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_date 
    FROM orders 
    WHERE customer_id = c.id 
    ORDER BY order_date DESC 
    LIMIT 1
) latest_orders;

Common Mistakes

  • Joining on non-indexed columns: Causes full table scans and poor performance.
  • Forgetting NULLs in OUTER joins: In a LEFT JOIN, right table columns will be NULL if no match exists. Filters like WHERE RightTable.ID > 0 will turn a LEFT JOIN into an INNER JOIN.
  • Misunderstanding CROSS JOIN: Producing accidental Cartesian products can hang a session or crash an application.

Follow-up Questions

  • What is a Hash Join? (Answer: A join algorithm where Postgres builds a hash table of the smaller relation for fast lookup).
  • Does PostgreSQL support FULL OUTER JOIN? (Answer: Yes, unlike some other databases like MySQL).

Expected Answer (MySQL 8.4)

MySQL supports standard ANSI joins but with some notable omissions:

  1. INNER JOIN: Intersection of sets.
  2. LEFT/RIGHT JOIN: One-sided inclusion.
  3. CROSS JOIN: Cartesian product.
  4. FULL OUTER JOIN: Not supported directly. Must be simulated using a UNION of a LEFT JOIN and a RIGHT JOIN.

Why It Matters

MySQL is the most common database for web applications. Understanding that it lacks FULL OUTER JOIN is a classic interview check. MySQL 8.0+ also introduced “Hash Joins,” which significantly improves performance for joins where no index is available.

SQL Example

-- Simulating a FULL OUTER JOIN in MySQL
SELECT * FROM TableA a LEFT JOIN TableB b ON a.id = b.id
UNION
SELECT * FROM TableA a RIGHT JOIN TableB b ON a.id = b.id;

Common Mistakes

  • Assuming FULL JOIN works: It will throw a syntax error.
  • Using comma syntax: SELECT * FROM A, B is a CROSS JOIN; it’s less readable and harder to maintain than explicit JOIN syntax.

Follow-up Questions

  • What is the Index Merge optimization? (Answer: When MySQL uses multiple indexes to satisfy a single query).
  • How to optimize a join? (Answer: Ensure join columns have indexes and compatible data types).

Expected Answer (SQL Server 2022)

SQL Server supports standard ANSI joins and specific hints.

  1. INNER JOIN: Matches in both tables.
  2. LEFT/RIGHT OUTER JOIN: One-sided matches.
  3. FULL OUTER JOIN: All rows from both sides.
  4. CROSS JOIN: Cartesian product.
  5. CROSS APPLY / OUTER APPLY: SQL Server’s equivalent to LATERAL joins, used for joining a table to a table-valued function or subquery that references the outer table.

Why It Matters

SQL Server 2022 introduced Intelligent Query Processing (IQP) enhancements that improve join performance automatically (e.g., Adaptive Joins). Choosing the correct join type ensures the optimizer can select the best algorithm (Nested Loops, Merge, or Hash).

SQL Example

-- CROSS APPLY example (similar to LATERAL)
SELECT c.CustomerName, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 1 OrderDate
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

Common Mistakes

  • Forcing Join Hints: Using INNER LOOP JOIN or INNER HASH JOIN manually often prevents the optimizer from adapting to data changes.
  • Null handling in Joins: Standard joins do not match NULL = NULL. You must use OR (a.col IS NULL AND b.col IS NULL) if NULL matching is required.

Follow-up Questions

  • What is an Adaptive Join? (Answer: An IQP feature that allows SQL Server to defer the choice between a Hash Join and a Nested Loop join until execution starts).
  • Difference between CROSS JOIN and CROSS APPLY? (Answer: CROSS JOIN joins two static sets; CROSS APPLY joins a set to a functionally dependent set).