SQL Joins

Left Join

Query all rows in the left-hand side, matching data from the right-hand side if possible but returning NULL data otherwise.

One common use case is joining derivative data back to the original table.

SELECT *
FROM people
LEFT JOIN (
           SELECT FamilyID, COUNT(*) AS FamilySize FROM people GROUP BY FamilyID
) AS r
ON people.FamilyID = r.FamilyID

Can take multiple right-hand side tables.

SELECT *
FROM people
LEFT JOIN (
           SELECT FamilyID, COUNT(*) AS FamilySize FROM people GROUP BY FamilyID
) AS r1
ON people.FamilyID = r1.FamilyID
LEFT JOIN (
           SELECT FamilyID, ID as HeadOfHouseholdID
           FROM (
                 SELECT FamilyID, ID, ROW_NUMBER() OVER (PARTITION BY FamilyID) AS row_number
                 FROM people
           )
           WHERE row_number = 1
) AS r2
ON people.FamilyID = r2.FamilyID

Tips

UPDATE FROM

In some circumstances, UPDATE FROM is a more appropriate option.

UPDATE people
SET LastName = r.LastName,
    FirstName = r.FirstName,
FROM (
      SELECT LastName, FirstName
      FROM people_pii
) AS r
WHERE people.ID = r.ID

ON vs WHERE

The following queries are not interchangeable: ON clauses are evaluated at match time, while WHERE clauses are evaluated after matching.

SELECT *
FROM transactions
LEFT JOIN people ON people.ID=transactions.ID
WHERE transactions.ID = 1

This will return only the rows in people where ID = 1.

SELECT *
FROM transactions
LEFT JOIN people ON people.ID=transactions.ID
    AND transactions.ID = 12345

This will return all rows in people, but joined columns will only be non-NULL for the rows where ID = 1.

CategoryRicottone