全部产品
云市场

JOIN

更新时间:2019-07-10 09:23:58

语法

  1. join_table:
  2. table_reference [INNER] JOIN table_factor [join_condition]
  3. | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  4. | table_reference CROSS JOIN table_reference [join_condition])
  5. table_reference:
  6. table_factor
  7. | join_table
  8. table_factor:
  9. tbl_name [alias]
  10. | table_subquery alias
  11. | ( table_references )
  12. join_condition:
  13. ON expression

示例

以下查询由FROM子句中的两个子查询联接组成,查询不同类别活动(音乐会和演出)的已售门票数和未售门票数。

  1. select catgroup1, sold, unsold
  2. from
  3. (select catgroup, sum(qtysold) as sold
  4. from category c, event e, sales s
  5. where c.catid = e.catid and e.eventid = s.eventid
  6. group by catgroup) as a(catgroup1, sold)
  7. join
  8. (select catgroup, sum(numtickets)-sum(qtysold) as unsold
  9. from category c, event e, sales s, listing l
  10. where c.catid = e.catid and e.eventid = s.eventid
  11. and s.listid = l.listid
  12. group by catgroup) as b(catgroup2, unsold)
  13. on a.catgroup1 = b.catgroup2
  14. order by 1;