Join Queries


A join is a SELECT statement that combines data from two tables, know as the left table and right table, into a single result.

  • combined by comparing columns from the left and right tables
    • usually using = operator
    • columns must have comparable data types
  • can compare any columns, but is usually a foreign key and a primary key

A natural join combines two tables by automatically matching columns that share the same name and have compatible data types.

  • eliminates duplicate columns from the result set
    • producing a streamlined table that contains only one copy of each matched column

Prefixes and Aliases

  • when duplicate column names appear in a join query, the names must be distinguished with a prefix
    • is the table name followed by a period
      • e.g., TableName.ColumnName
  • to simplify prefixes, column names can be replaced with an alias
    • follows the column name, separated by an optional AS keyword
      • SELECT ColumnName AS alias

Example

SELECT Department.Name AS Team, 
       Employee.Name AS Supervisor
FROM Department, Employee
WHERE Manager = ID;

Inner and Full Joins

A join clause determines how a join query handles unmatched rows.

  • 2 common join clauses:
    • INNER JOIN
      • selects only matching left and right table rows
    • FULL JOIN
      • selects all left and right table rows, regardless of match
      • unmatched left table rows appear with NULL values in right table columns and vice versa
  • join clause appears between FROM clause and ON clause
    • FROM clause specifies left table
    • INNER JOIN or FULL JOIN specifies right table
    • ON clause specifies the join columns
    • optional WHERE clause follows ON
  • MySQL does not support FULL JOIN

Example

SELECT Department.Name AS Team,
		Employee.Name AS Supervisor
FROM Department
INNER JOIN Employee
ON Manager = ID

Left and Right Joins

LEFT JOIN selects all left table rows, but only matching right table rows.

RIGHT JOIN selects all right table rows, but only matching left table rows.

An outer join is any join that selects unmatched rows.

  • includes left, right, and full joins

Alternative Join Queries

  • inner joins can be written without a JOIN keyword
  • outer joins can be written with a UNION keyword instead

UNION combines the results of two SELECT clauses into one result table.

  • for a left join,
    • one SELECT returns matching rows
    • another returns unmatched left table rows
  • for a right join,
    • one SELECT returns matching rows
    • another returns unmatched right table rows
  • for a full join,
    • three SELECT clauses are necessary
    • one returns matching rows
    • another returns unmatched left table rows
    • a third return unmatched right table rows
    • the three results are merged with two UNION keywords
  • using JOIN is better practice than UNION
    • because union is more complex and difficult to understand

Example