Chapter 6 SQL Databases

Join - A relational operation where two or more tables with a common domain combine into a single table/ view.

Equi-join - A join where the condition is based on equality between values in the common columns, which appear redundantly in the result table.

Example of Equi-join:

SELECT Customer_T.CustomerID, Order_T.CustomerID,

CustomerName, OrderID

    FROM Customer_T, Order_t

        WHERE Customer_T.CustomerID = Order_T.CustomerID

        ORDER BY OrderID

Inner join - matches primary and foreign keys and only returns rows from each table that have matching rows in the other

Natural inner-join - Equi-join where one of the duplicate columns is eliminated in the result table

Outer Join - a join where rows that don’t have common columns are included in the result table regardless.

Union join - Includes all data from each table that was joined

Left Outer Join - causes rows from the first-mentioned table to appear even if there is no corresponding data. Includes rows with no matching order rows.

Self Join - Involves tables that implement 1-many unary relationships

Subquery - placing an inner query (SELECT statement) inside an outer query, usually placed in the WHERE or HAVING clause of the outer query.

Subqueries can be: Noncorrelated - executed once for the entire outer query or Correlated - executed once for each row returned by the outer query

Noncorrelated subqueries:

• Dont depend on data from the outer query

• Execute once for the entire outer query

Correlated subqueries:

• Make use of data from the outer query

• Execute once for each row of the outer query

• Can use the EXISTS and ALL operators

Things to improve Query Efficiency:

• Limit the number of subqueries; try to make everything done in a single query if possible

• If data is to be used many times, make a separate query and store it as a view

• Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set