Joins combine data from multiple, related data sources is a single result set. Join expressions appear in the WHERE clause.

Inner Join

This join returns only the rows from both tables that match based on the join criteria.

Syntax

dataSource1 [ INNER ] JOIN dataSource2 ON ( joinExpression )

Arguments

  • dataSource1: A valid data source, such as a table or view.

  • dataSource2: A valid data source, such as a table or view.

  • joinExpression: An expression that evaluates to true or false, used to determine when rows match. Often this expression takes the form of dataSource1.column = dataSource2.column, where rows are returned if the two values match.

Examples

SELECT uo11.id, uo11.salesAmt, uo12.salesAmt
FROM MYSQL.inventory.userOrders2011 uo11 INNER JOIN MYSQL.inventory.userOrders2012 uo12
ON (uo11.id=uo12.id);

LEFT OUTER JOIN

This join matches values from two different data sources, keeping data from the first data source and only data from the second data source that matches rows in the first data source.

Syntax

dataSource1 LEFT [ OUTER ] JOIN dataSource2 ON ( joinExpression )

Arguments

  • dataSource1: A valid data source, such as a table or view.

  • dataSource2: A valid data source, such as a table or view.

  • joinExpression: An expression that evaluates to true or false, used to determine when rows match. Often this expression takes the form of dataSource1.column = dataSource2.column, where rows are returned if the two values match.

Examples

SELECT u.id, u.firstName, u.lastName, a.address1, a.city, a.state, a.zipcode
FROM MYSQL.inventory.user u LEFT OUTER JOIN MYSQL.inventory.address a
ON (u.id=a.id);

RIGHT OUTER JOIN

This join matches values from two different data sources, keeping data from the second data source and only data from the first data source that matches rows in the second data source.

Syntax

dataSource1 RIGHT [ OUTER ] JOIN dataSource2 ON ( joinExpression )

Arguments

  • dataSource1: A valid data source, such as a table or view.

  • dataSource2: A valid data source, such as a table or view.

  • joinExpression: An expression that evaluates to true or false, used to determine when rows match. Often this expression takes the form of dataSource1.column = dataSource2.column, where rows are returned if the two values match.

Examples

SELECT u.id, u.firstName, u.lastName, a.address1, a.city, a.state, a.zipcode
FROM MYSQL.inventory.address a RIGHT OUTER JOIN MYSQL.inventory.user u
ON (a.id=u.id);

CROSS JOIN

This join type returns the Cartesian product of two joined tables.

Syntax

dataSource1 CROSS JOIN dataSource2

Arguments

  • dataSource1: A valid data source, such as a table or view.

  • dataSource2: A valid data source, such as a table or view.

Examples

SELECT * FROM MYSQL.inventory.products CROSS JOIN MYSQL.inventory.retailers;