JOIN (SQL)

• Creates a Cartesian Product between two tables

  SELECT <...>
  FROM table1
  CROSS JOIN table2
  -- (...)

  -- or via SQL 89, theta-style syntax,
  -- however, this is discouraged.

  SELECT <...>
  FROM table1, table2
  -- (...)

• Creates a result set that contains all combinations of a query filtering of the left side with the matching rows of the right side.

  SELECT <...>
  FROM table1 t1
  INNER JOIN table2 t2
  ON t1.colM = t2.colN
  -- (...)

  -- Just like the CROSS JOIN example, you can also use the
  -- theta-style syntax with a WHERE clause, but that's also
  -- discouraged. Favor the ANSI SQL standard.

  SELECT <...>
  FROM table1, table2
  WHERE t1.colM = t2.colN
  -- (...)

• LEFT

  SELECT <...>
  FROM table1 t1
  LEFT JOIN table2 t2
  ON t1.colM = t2.colN

• RIGHT

  SELECT <...>
  FROM table1 t1
  RIGHT JOIN table2 t2
  ON t1.colM = t2.colN

• FULL

  Creates a result set based on a filter that uses both the left and right sides, no matter if matches exist.

  SELECT <...>
  FROM table1 t1
  FULL JOIN table2 t2
  ON t1.colM = t2.colN

A lateral join is a type of join in SQL that allows you to join a table with a subquery, where the subquery is run for each row of the main table. (Ferrari and Pirozzi 2023, 129)

• i.e. LATERAL JOIN allows a subquery to cross reference rolls from outter queries (a FROM clause) while joining the subquery and the outer query result sets.

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.