3.4

Database Reports

• Reports in relational databases are created from data spanning multiple tables.

• Multi-table reports utilize JOIN statements to combine data from different tables (also known as left and right tables).

  • JOIN: A SELECT statement that merges data from two tables into a single result set.

  • The combination of tables occurs by comparing columns from the left and right tables using a comparison operator.

  • Columns involved in the join must share compatible data types.

  • Typically, joins compare a foreign key from one table against the primary key of another.

  • However, joins can compare any columns provided they have comparable data types.

Table Identifiers in Queries

• To avoid ambiguity in queries, it is important to distinguish between columns from different tables.

  • The standard prefixing format involves using the table name followed by a period (e.g., TableName.ColumnName).

• For readability, column names can be replaced with an alias.

  • The alias follows the column name, optionally preceded by the AS keyword.

Types of Joins

• Different types of joins handle matching and unmatched rows in specific ways:

  • INNER JOIN: Selects only matching rows from both the left and right tables.

  • FULL JOIN: Selects all rows from both tables, including unmatched rows from either side.

  • LEFT JOIN: Picks all rows from the left table, and only matching rows from the right table.

  • RIGHT JOIN: Selects all rows from the right table and only matching rows from the left table.

  • OUTER JOIN: A general term for joins that include unmatched rows from either side (e.g., left, right, or full joins).

JOIN Clause Structure

• The JOIN clause is structured within the SQL command as follows:

  • The FROM clause indicates the left table.

  • The JOIN clause specifies the type of join (INNER or FULL JOIN) with the right table.

  • The ON clause identifies how the tables are connected through the specific columns.

  • An optional WHERE clause may follow the ON clause to further filter results.

Result Set Merging with UNION

• The UNION keyword is used to combine the results of two SELECT statements into a single result set.

• When using the LEFT JOIN, it retrieves all rows from the left table without regard to matching rows in the right table.

• Unmatched rows from either side require specific handling using UNION clauses to combine results appropriately:

  • Returns unmatched rows from either the left table or right table as necessary.

• Two UNION keywords can be utilized to merge up to three distinct result sets.