Hosting your data in a relational data model has both benefits and drawbacks. Since your data now is spread across many tables, the data must be constructed into a data set to be of use. The JOIN operator provided by SQL facilitates the process. It allows joining data sets gathered from the tables on some meaningful criteria, often primary and foreign keys.
Each JOIN operator involves two tables, and there is no practical limit on how many JOIN(s) a query could contain or how many tables could be JOIN(ed) in a query. The standard JOIN types are INNER JOIN, LEFT and RIGHT OUTER JOIN, and FULL JOIN. They refer to the way the data sets from participating tables are matched.
While JOIN(s) combine data sets horizontally, the UNION operator combines them vertically, producing a single list of records from the different queries.
When JOIN and UNION operators are used in a query, in addition to a variety of clauses, the query could quickly become complex. This complexity could be abstracted using the VIEW construct; a VIEW is a frozen query. In addition to hiding complexity, they also could be used to address data security, as well as data access customization.
The concepts introduced and elaborated in this chapter are, by and large, part of the SQL Standard, yet there are significant differences between different RDBMS implementations.