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
- usually using
- 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
- e.g.,
- is the table name followed by a period
- to simplify prefixes, column names can be replaced with an alias
- follows the column name, separated by an optional
ASkeywordSELECT ColumnName AS alias
- follows the column name, separated by an optional
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
NULLvalues in right table columns and vice versa
- join clause appears between
FROMclause andONclauseFROMclause specifies left tableINNER JOINorFULL JOINspecifies right tableONclause specifies the join columns- optional
WHEREclause followsON
- 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
JOINkeyword - outer joins can be written with a
UNIONkeyword instead
UNION combines the results of two SELECT clauses into one result table.
- for a left join,
- one
SELECTreturns matching rows - another returns unmatched left table rows
- one
- for a right join,
- one
SELECTreturns matching rows - another returns unmatched right table rows
- one
- for a full join,
- three
SELECTclauses 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
UNIONkeywords
- three
- using
JOINis better practice thanUNION- because union is more complex and difficult to understand
Example
