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 (Left Outer Join)

Returns records from left table + records that satisfy the condition from the right table.

  • Records without matching values in the right table, the result set will contain the Null values.
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

Returns records that have matching values in both tables.

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

FULL JOIN

Grabs records from both tables, joins them on a condition.

If there are records that does not satisfy the join condition, each records on both tables show up as individual records will null for other table's columns.

Many-to-Many Relationship

Establish many-to-many relationship where a relationship between two tables may not be one-to-one.

You need to create a third table that will hold all combinations of the many-to-many relationships.

This table will then perform a join with each original table to form a relationship.

  • You would need two join statements to accomplish this.