Introduction

In the previous chapter, we saw how the various types of SQL joins can connect multiple tables in a single SELECT query. This powerful technique gives us fine-grained control over the data that our SQL queries return. However, SQL offers much more. In this chapter, we'll use the PACKT_ONLINE_SHOP database to see how subqueries, CASE statements, and views boost the flexibility and power of SQL in an efficient, intuitive way. Like joins, subqueries can relate different tables together in the same SQL query, but compared to queries involving multiple outer joins and unions, subqueries can offer a simpler, cleaner query structure. This can make the development and maintenance of the code easier. CASE statements offer a clean, efficient way for a query to handle decisions, depending on the defined conditions that occur in the query. Views provide an efficient, effective way to group multiple tables together in a symbolic table that's available for use by other queries...