SQL design traps
Sometimes, we might find that our query returns the wrong number of records or displays wrong figures. A logical step that we need to follow in that case is to analyze our SQL statement and make sure that there are no missing or no extra joins. The syntax analysis of the query is correct, and it returns a result. The main problem here is that we had the wrong number of records or wrong results, which are different from what we expected. This can happen if you have been trapped by one of the SQL design traps that we will discuss in this section.
Design traps appear when the queries return correct results but the cardinalities between the entities are not well defined. The join might seem to be logical at first glance, but after a deeper analysis we can find that it is not logical to join these tables together. Normally, we need at least three tables and two joins to have a trap. In this chapter, we will discuss three types of traps, but before that let's consider the following...
