SQL Joins Summary

FROM Clause

• Specifies tables involved in the query.
• Can include subqueries (must have aliases).
• Supports implicit and explicit joins.

Implicit JOIN

• Tables from other schemas should be qualified or use search_path.
• Example:

  SELECT S.playernr
  FROM Players as S, else scheme.Residences as City
  WHERE S.place = City.place name;

search_path

• Use SHOW search_path; to view the current path.
• Use SET search_path = public, myschema; to set a new path.
• Caution: Beware of tables with the same name in different schemas.

Explicit INNER JOIN

• Uses INNER JOIN ... ON (.. operator ...).
• Represents a cross-section, common in SQL.
• Example:

  SELECT s.playernr
  FROM players AS s INNER JOIN otherscheme.domiciles AS city
  ON (s.place = city.place name);

Explicit FULL OUTER JOIN

• Retains all rows from both tables.
• USING clause assumes equal column names.
• Difference between USING and ON:
  • USING: only one row with the specified column remains.
  • ON: two rows with the specified column from both tables.
• Example:

  SELECT players.playernr, name, amount
  FROM players FULL OUTER JOIN penalties
  USING (player no);

Explicit LEFT OUTER JOIN

• Retains all rows from the left table.
• Example:

  SELECT players.playernr, name, amount
  FROM players LEFT OUTER JOIN fines
  USING (player number);

OUTER JOINS

• LEFT OUTER JOIN: All rows from the left-hand table with matching data from the right-hand table; otherwise, NULL values.
• RIGHT OUTER JOIN: All rows from the right-hand table with matching data from the left-hand table; otherwise, NULL values.
• FULL OUTER JOIN: All rows from both tables, with matching data; otherwise, NULL values.

Conditions: FROM vs. WHERE

• Conditions in ON clause affect the join; conditions in WHERE clause filter the result.
• Example:

  SELECT teams.playernr, teams.teamnr, paymentnr
  FROM teams LEFT OUTER JOIN penalties
  ON (teams.playernr = fines.playernr)
  WHERE division = 'second'

vs.

  SELECT teams.playernr, teams.teamnr, paymentnr
  FROM teams LEFT OUTER JOIN penalties
  ON (teams.playernr = penalties.playernr) AND division = 'second'

Query Example: Players Never Fined 50 Euros

• Solution 1 (Uncorrelated):

  SELECT *
  FROM players
  WHERE player number NOT IN
  (SELECT player no.
  FROM fines
  WHERE amount = 50);

• Solution 2 (JOIN):

  SELECT *
  FROM players s LEFT OUTER JOIN fines b
  ON (s.playernr = b.playernr AND b.amount = 50)
  WHERE b.playernr IS NULL

CROSS JOIN

• Explicit Cartesian product.
• Example:

  SELECT *
  FROM teams CROSS JOIN penalties;

UNION JOIN

• Not supported by PostgreSQL.
• Each row of each table is included once, completed with NULL values.

NATURAL JOIN

• Lexicographic join.
• Example:

  SELECT *
  FROM teams NATURAL INNER JOIN penalties
  WHERE division = 'ere';

EQUI/THETA JOIN

• EQUI JOIN: Equation with =. Example: ON A.id = B.id
• THETA JOIN: Comparison with another comparison operator (<>,
• Example (THETA JOIN):

  SELECT s.playernr, s.name, count(sp.playernr)
  FROM players s INNER JOIN players sp
  ON (s.playernr <> sp.playernr)
  GROUP BY s.playernr, s.name

Another Example (THETA JOIN):

  SELECT s.playernr, s.name, count(sp.playernr)
  FROM players s INNER JOIN players sp
  ON (s.playernr <> sp.playernr)
  WHERE length(s.name) = length(sp.name)
  GROUP BY s.playernr, s.name

JOIN Syntax Summary

• (NATURAL)
• LEFT
• RIGHT
• INNER
• OUTER
• ON (.. op ..)
• USING (..)
• (CROSS) (No ON or USING)
• FULL
• (UNION)

Visual Summary of Joins

• Includes diagrams for LEFT JOIN, INNER JOIN, RIGHT JOIN, and FULL OUTER JOIN.