Querying Across Tables
Time to put our newfound knowledge of relational algebra into practice! One of the great advantages of a relational database is the ability to mix tables together in queries, and aggregate or group across columns. Databases are built to do this kind of work, so it's often much faster to build the right query than to move similar logic into our application code.
-
Querying Across TablesInner and outer JOINs
There are five types of joins in most relational database systems, but we can get away with focusing almost entirely on the two categories: INNER and OUTER joins. We'll learn about the distinction between these two types, and how to pick the right join operation for the job.
-
Querying Across TablesEXERCISE: JOIN to replace ids with names
There are several places in our app where alphanumeric IDs are shown to users. Humans prefer referring to things by names, so let’s use JOIN to transform these references into records that are more user-friendly!
-
Querying Across TablesAggregate Functions and GROUP BY
Often, we are interested in summary data that is aggregated over a result set (example: “give me the number of products we have in each category”). Through using GROUP BY, we can define the records we are interested in. We can use aggregate functions like sum, count, group_concat to aggregate over duplicate data.
-
Querying Across TablesEXERCISE: Aggregate Functions and GROUP BY
There are several places where some additional aggregate information is needed in order to “fix” the currently broken experience. Firstly, we need to get the subtotal of an order’s line items and display it prominently at the bottom of the order page. Then, we'll count and concatenate aggregate results as we group records on the employee, customer, and product list pages.