Table Relationships & JOINs (INNER, LEFT, RIGHT)

« INSERT, UPDATE, DELETE & Upsert (ON DUPLICATE KEY)« Previous Aggregations, GROUP BY & The HAVING Clause »Next »

Connect normalized relational tables using foreign keys and perform high-performance INNER, LEFT, and RIGHT JOINs.

Relational Foreign Keys & Referential Integrity

Foreign keys enforce referential integrity between tables. A record in Orders cannot reference a non-existent UserID.

INNER JOIN: Returns records that have matching values in both tables.

LEFT JOIN: Returns all records from the left table, and matched records from the right table (unmatched columns are NULL).

Multi-Table JOIN with Aggregation

Try It Yourself »

SELECT 
    u.UserID,
    u.Username,
    COUNT(o.OrderID) AS TotalOrders,
    COALESCE(SUM(o.OrderTotal), 0.00) AS LifetimeSpend
FROM Users u
LEFT JOIN Orders o ON u.UserID = o.UserID
GROUP BY u.UserID, u.Username
ORDER BY LifetimeSpend DESC;

Google AdSense Slot (inContentBanner)

Set your Publisher ID in src/config/ads.ts to display live ads.

Test Yourself With Exercise

+15 XP

Which type of JOIN guarantees that all rows from the primary left table appear in the results even if there are no corresponding matches in the right table?

« Previous: INSERT, UPDATE, DELETE & Upsert (ON DUPLICATE KEY)Next: Aggregations, GROUP BY & The HAVING Clause »