HomeToolsAbout

Joins

What is it

Join returns all records that match the JOIN and WHERE criteria and any GROUP BY or HAVING clauses

  • If you want unique records, you have to specify the record wanted through additional conditions or SELECT DISTINCT

LEFT JOIN

SELECT c.customer_name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

When a customer_id in Customers table matches customer_id in Orders table, the records matching from each table are "joined" together as a single row record.

LEFT JOIN ensures that all rows from the Customers table are returned, regardless of whether there’s a match in the Orders table

INNER JOIN

SELECT t1.* FROM "Cars" t1
INNER JOIN "Dealership" t2 
  ON t2._vehicleBrand = ANY(t1."carBrands")
WHERE t2."State" = 'NewYork' AND t2."zipCode" = '11101'

Inner Join fetches all records that match a condition between the two tables with a single-column as a reference overlap (join condition) from each table

  • There can only be one column referenced as a join condition from each table for inner join
    • e.g. t1.id and t2.userId when t1 is a users table

If you want to further narrow-down a join result, you have to define the criteria in the WHERE clause

  • because you can't reference two columns as a join condition

Lateral Join (Left Join Lateral)

Allows a subquery in the FROM clause to reference columns from preceding tables.

  • references any column from the outer table

Left Join simply combines the rows from two tables based on a condition while lateral join evaluates the subquery on every row of the outer table.

Also see - correlated subqueries (not the same, but similar)

SELECT c.customer_id, c.customer_name, o.latest_order_id
FROM Customers c
LEFT JOIN LATERAL (
    SELECT order_id AS latest_order_id
    FROM Orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) o ON true;

Subqueries vs Join

Sub-queries are the logically correct way to solve problems of the form, "Get facts from A, conditional on facts from B".

  • In such instances, it makes more logical sense to stick B in a sub-query than to do a join.
  • It is also safer, in a practical sense, since you don't have to be cautious about getting duplicated facts from A due to multiple matches against B.

Practically speaking, however, the answer usually comes down to performance. This depends on the optimizers.

Historically, explicit joins usually win, hence the established wisdom that joins are better.

From MySQL manual:

"A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better — a fact that is not specific to MySQL Server alone."

AboutContact