Analyze Cross-Table Data with JOIN Queries in AnalyticDB for MySQL - AnalyticDB for MySQL

Use the JOIN clause to combine rows from two or more tables based on a related column.

Syntax

join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition]

table_reference:
    table_factor
  | join_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON expression

Supported JOIN types

JOIN type	Returns
`INNER JOIN`	Only rows with matching values in both tables.
`LEFT [OUTER] JOIN`	All rows from the left table, plus matching rows from the right table. Non-matching rows from the right table appear as NULL.
`RIGHT [OUTER] JOIN`	All rows from the right table, plus matching rows from the left table. Non-matching rows from the left table appear as NULL.
`FULL [OUTER] JOIN`	All rows from both tables. Non-matching rows from either side appear as NULL.
`CROSS JOIN`	The Cartesian product of both tables—every combination of rows. Use a WHERE clause to filter results.

Example

The following query joins two subqueries to report the number of sold and unsold tickets per event category.

SELECT catgroup1, sold, unsold
FROM
  (SELECT catgroup, SUM(qtysold) AS sold
   FROM category c, event e, sales s
   WHERE c.catid = e.catid AND e.eventid = s.eventid
   GROUP BY catgroup) AS a(catgroup1, sold)
JOIN
  (SELECT catgroup, SUM(numtickets) - SUM(qtysold) AS unsold
   FROM category c, event e, sales s, listing l
   WHERE c.catid = e.catid AND e.eventid = s.eventid
     AND s.listid = l.listid
   GROUP BY catgroup) AS b(catgroup2, unsold)
ON a.catgroup1 = b.catgroup2
ORDER BY 1;

The first subquery aggregates sold ticket counts from the category, event, and sales tables. The second subquery aggregates unsold ticket counts from the category, event, sales, and listing tables. The outer query joins them on catgroup and returns the sold and unsold counts side by side.