As the number of tables in a query grows, the amount of effort required to carry it out increases rapidly. The SQL language itself places no limit on the number of tables joined by a query. Some SQL products do limit the number of tables, with a limit of about eight tables being fairly common. The high processing cost of queries that join many tables imposes an even lower practical limit in many applications.
In online transaction processing (OLTP) applications, it’s common for a query to involve only one or two tables. In these applications, response time is critical—the user typically enters one or two items of data and needs a response from the database within a second or two. Here are some typical OLTP queries for the sample database:
- The user enters a customer number into a form, and the DBMS retrieves the customer’s credit limit, account balance, and other data (a single-table query).
- A cash register scans a product number from a package and retrieves the product’s name and price from the database (a single-table query).
- The user enters a salesperson’s name, and the program lists the current orders for that salesperson (a two-table inquiry).
In decision-support applications, by contrast, it’s common for a query to involve many different tables and exercise complex relationships in the database. In these applications, the query results are often used to help make expensive decisions, so a query that requires several minutes or even several hours to complete is perfectly acceptable. Here are some typical decision-support queries for the sample database:
- The user enters an office name, and the program lists the 25 largest orders taken by salespeople in that office (a three-table query).
- A report summarizes sales by product type for each salesperson, showing which salespeople are selling which products (a three-table query).
- A manager considers opening a new Seattle sales office and runs a query analyzing the impact on orders, products, customers, and the salespeople who call on them (a four-table query).
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.